=LEN(a1)-LEN(SUBSTITUTE(a1," ",""))+1

If you want to count the total words in a cell, you can do so with a formula that uses the LEN and SUBSTITUTE functions. In the generic form above, a1 represents the address of the cellĀ containing words being counted.

In the example, we are using this formula:

=LEN(TRIM(B3))-LEN(SUBSTITUTE(B3," ",""))+1

SUBSTITUTE removes all spaces from the text, then LEN calculates the length of the text without spaces. This number is then subtracted from the length of the text with spaces, and the number 1 is added to the final result, since the number of words is the number of spaces + 1.

What about TRIM, what's it doing in the formula? TRIM removes an extra spaces between words, or at the beginning or end of the text. So, before the length of the text with spaces is calculated, we use TRIM to make sure the text doesn't contain any extra spaces, since extra spaces will throw the word count off.

The formula in the example shown will return 1 even if a cell is empty. If you need to guard against this problem, using IF and ISBLANK: