Did you know excel can limit the number of words you enter in a cell?
Have thought of how you can limit the number of words in an excel cell? Ms. Excel LEN function counts the number of characters entered into an excel cell hence can be used in limiting characters entered into an excel cell (Data Validation). There is no specific function in excel that counts the number of words. Nevertheless, LEN function can be customized to count the number of words in a cell. It can then be used for data validation in limiting the number of words in cell.
Ms. Excel LEN Function for Counting Number of Words
A customized Ms. Excel formula could be used to count the number of words in a cell. The formula is =LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))+1. It counts the number of spaces in a cell and adds one to it. This formula can help you restrict the number of words entered in cell using data validation.
Assume you only want restrict users from entering more than two word in the Name field.
Procedure in Ms. Excel
- First label the column for name values.
2. Highlight column A from cell then navigate to data tab in the menu bar and select data validation option. A dialogue box will pop-up then under the allow drop down select custom.
3. In cell in the formula section enter the following formula =LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))+1.
Since you do not need to restrict the number of words in the column label, clear the data validation for cell A1.
By restricting the data entered into your spreadsheet you enhance data integrity and completeness. In the long run, you save your time during data cleansing process because only the right cell values will be entered. This procedure can also be applied in the user forms to restrict the number of words entered in a cell.
To learn this and more tips,sign up for an upcoming Advanced Excel Training : <See More Details>
Upcoming Program Details:
- Start Date: 5th May 2018
- End Date: 26th May 2018
- Duration – Four(4) Consecutive Saturdays (8:00 am to 4:00 pm)
- Normal Investment: Kes. 40,000 (Inclusive VAT)
- Early Bird Investment: Kes. 30,000 (For the first 15 delegates to pay up before 27th April 2018)
- Group Offer: Register/Refer a group of five(5) delegates and get one(1) additional slot free
- Venue: InQuest Corporate Training Centre, Gracecourt – Parklands Nairobi, Kenya (Non Resident – Ample parking available)