Explanation
Excel doesn't have a dedicated function for counting words in a cell. However, with a little ingenuity, you can create such a formula using the SUBSTITUTE and LEN functions, with help from TRIM, as shown in the example. At a high level, this formula uses the LEN function to count the number of characters in the cell, with and without spaces, then uses the difference to figure out the word count. This works, because word count is equal to the number of spaces + 1, so long as there is one space between each word.
The first part of the formula counts the characters in cell B5, after removing extra space:
=LEN(TRIM(B5)) // normalize space, count characters
Inside LEN, the TRIM function first removes any extra spaces between words, or at the beginning or end of the text. This is important, since any extra spaces will throw off the word count. In this case, there are no extra space characters, so TRIM returns the original text directly to the LEN function, which returns 30:
LEN("All Quiet on the Western Front") // returns 30
At this point, we have:
=30-LEN(SUBSTITUTE(B5," ",""))+1
Next, we use the SUBSTITUTE function to remove all space characters from the text:
SUBSTITUTE(B5," ","") // strip all space
Notice SUBSTITUTE is configured to look for a space character (" "), and replace with an empty string (""). By default, SUBSTITUTE will replace all spaces. The result is delivered directly to the LEN function, which returns the count:
LEN("AllQuietontheWesternFront") // returns 25
LEN returns 25, the number of characters remaining after all space has been removed. We can now simplify the formula to:
=30-25+1 // returns 6
which returns 6 as a final result, the number of words in cell B5.
Dealing with blank cells
The formula in the example will return 1 even if a cell is empty, or contains only space. This happens because we are adding 1 unconditionally, after counting space characters between words. To guard against this problem, you can adapt the formula as shown below:
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0)
Notice we've replaced 1 with this expression:
LEN(TRIM(B5))>0
This code first trims B5, then checks the length. If B5 contains text, LEN returns a positive number, and the expression returns TRUE. If B5 is empty, or contains only space, TRIM returns an empty string ("") to LEN. In that case, LEN returns zero (0) and the expression returns FALSE. The trick is that TRUE and FALSE evaluate to 1 and zero, respectively, when involved in any math operation. As a result, the expression only adds 1 when there is text in B5. Otherwise, it adds zero (0). This logic could also be written with the IF function statement like this:
IF(LEN(TRIM(B5))>0,1,0)
and the result would be the same. The expression above is simply more compact.
