Count total words in a cell

An excel formula to count total words in a cell

 Count total words in a cell
=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:


How the formula works

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.

Dealing with extra spaces

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.

Dealing with empty cells

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:


0 votes. 0 / 5