اكسل

To count words from a cell you need to combine LEN function with SUBSTITUTE function. And the formula will be (Text is in cell A1):

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

When you refer to a cell using this formula, it will return 7 in the result. And yes, you have a total of 7 words in the cell.

how it works

Before getting into this formula just think this way. In a normal sentence if you have eight words then you will definitely have 7 spaces in those words.

Right?

That means you will always have one word more than the spaces. The idea is simple: If you want to count the words, count the spaces and add one in it. Now, to understand this formula you need to split it into three parts.

In the first part, you have used LEN function to count the numbers of characters from the cell A1.

And in the second and third part, you have combined SUBSTITUTE with LEN to remove spaces from the cell and then count the characters.

At this point, you have an equation like this.

The total number of characters with spaces and the total number of characters without spaces.

And when you subtract both of these numbers gets the number of spaces and in the end, you have to add one in it. It returns 7 in the result which is the total number of words in the cell.

⚠️ Important

When you use the above formula it will return 1 even if the cell is blank so it’s better to wrap it with IF function to avoid this problem.

=IF(ISBLANK(A2),0,LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)

This formula will first check the cell and only return word count if there is a value in the cell.

Using a UDF

Apart from the above formulas, I have written a small code to create a UDF for this. This code will help you to create a custom function which will simply return the word count.

In short, you don’t need to combine any functions.

Function MyWordCount(rng As Range) As Integer

MyWordCount = UBound(Split(rng.Value, " "), 1) + 1

End Function

Let me tell you how to use it.

• First of all, enter this code in VBA editor.

• And then come back to your worksheet, and enter “=MyWordCount(” and refer to the cell in which you have value.

And, it will return the word count.