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.