The formula returns the word count in cell B5. The first part of the formula counts the characters in cell B5, after removing extra space: 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: At this point, we have: Next, we use the SUBSTITUTE function to remove all space characters from the text: 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 returns 25, the number of characters remaining after all space has been removed. We can now simplify the formula to: 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: Notice we’ve replaced 1 with this expression: 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: and the result would be the same. The expression above is simply more compact.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
