Microsoft Office Tutorials and References
In Depth Information
Advanced Text Formulas
Counting the number of words in a cell
The following formula returns the number of words in cell A1:
=LEN(TRIM(A1))-LEN(SUBSTITUTE((A1),” “,””))+1
The formula uses the TRIM function to remove excess spaces. It then uses the SUBSTITUTE
function to create a new string (in memory) that has all the space characters removed. The length of
this string is subtracted from the length of the original (trimmed) string to get the number of
spaces. This value is then incremented by 1 to get the number of words.
Note that this formula returns 1 if the cell is empty. The following modification solves that problem:
=IF(LEN(A1)=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),” “,””))+1)
Excel has many functions that work with text, but you’re likely to run into a situation in
which the appropriate function just doesn’t exist. In such a case, you can often create
your own worksheet function using VBA. Chapter 25 also contains a number of custom
text functions written in VBA.
Search JabSto ::

Custom Search