Count total words in a cell

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:

Generic Formula:

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

…….where A1 represents the cell of interest.

Example:

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:

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

 

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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar