Counting the number of words in a cell

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