Excel 2010 Bible

Part II: Working with Formulas and Functions

248

The formula is rather complex because it must determine whether the number will end in th, st,

nd, or rd. This formula also uses literal arrays (enclosed in brackets), which are described in

Chapter 17.

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 func-

tion 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 will return 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)