How to count total words in a cell
If you have a sentence or text in a cell, it very easy to do the count of words with automatically using a formula that include three functions LEN, TRIM, and SUBSTITUTE. Here is the general formula:
…….where A1 represents the cell of interest.
Assuming that you’ve got a text or a sentence in a cell and you don’t want to count total words in a cell, you should also consider this formula to do the work for you. See how we have done it in this example.
Figure 1. Example 1 of Count total words in a cell
From this example, the formula in the highlighted cell (C5) is:
Here’s How the Formula works
The SUBSTITUTE gets rid of all spaces from the sentences or text while LEN comes in to calculate the length of the sentences of text without spaces. To get the actual count of words in cells (B5), the result from LEN’s calculation is subtracted from the count of the text with space. The result of the subtraction is then added number 1. That is because we know that the number of words is always equivalent to the count of spaces plus 1.
TRIM function gets rid of all extra spaces be it between words, beginning and the end of the text. However, if the cell is empty, the formula returns 1 and not zero. To prevent such issues, you need to introduce IF and ISBLANK. This is how the formula would look like:
= IF(ISBLANK(A1),0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1