Count Words in Excel [Cell + Range + Worksheet]

There is one option which I always wish Excel should have.

That’s ==> Count Number of Words.

If you work in Microsoft Word, there is an inbuilt option on the status bar which shows you how many words are there in the sheet.

But, when it comes to Excel there is no such option to count words. You can count the number of cells which have text but not actual words in them.

Here’s the kicker:

As you know, in Excel, we have formulas and you can use them to calculate almost everything.

Yes, you can count words using formulas.

So today, in this post, you will learn how to count words in Excel from a cell, or a range of cells or even from the entire worksheet.

And, we will also learn that how to count a specific word from a range of cells. Now without any ado, let's get started.

1. Formula to Count Words from a Cell

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.

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. So 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 added one in it.

It returns 7 in the result which is the total number of words in the cell.

Important Note:

When you use 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 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 count.

In short, you don’t need to combine any functions. So, let me tell you how to use it.

Function MyWordCount(rng As Range) As Integer
MyWordCount = UBound(Split(rng.Value, " "), 1) + 1
End Function

• Enter this code in VBA editor.

• 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.

2. Count Words from a Range of Cells

Now let’s come to the next level.

And, here you need to count words from a range of cells instead of a single cell.

The good news is you just need to use the same formula which you have used above with a small amendment.

And, the formula will be:

=SUMPRODUCT(LEN(A1:A11)-LEN(SUBSTITUTE(A1:A11," ",""))+1)

In above formula, A1:A11 is the range of cells. And, when you enter the formula will return 77 in the result.

How does it work

This formula works in the same way as first method works but just a small advanced.

The only difference is you have wrapped it in SUMPRODUCT and refer to the entire range instead of a single cell.

Do you remember that SUMPRODUCT can take arrays?

So when you use it, it returns an array where you have a count of words for each cell.

And in the end, it sums those counts and tells you the count of words in the column.

Related: SUMPRODUCT IF

3. Word Count from Entire Worksheet with VBA Code

This code is one of my useful macro codes list which I use in my work. It can help you to count all the words from a worksheet.

Sub Word_Count_Worksheet()
Dim WordCnt As Long
Dim rng As Range
Dim S As String
Dim N As Long
For Each rng In ActiveSheet.UsedRange.Cells
S = Application.WorksheetFunction.Trim(rng.Text)
N = 0
If S <> vbNullString Then
N = Len(S) - Len(Replace(S, " ", "")) + 1
End If
WordCnt = WordCnt + N
Next rng
MsgBox "There are total " & Format(WordCnt, "#,##0") & " words in the active worksheet"
End Sub

When you run it, it will show a message box with the number of words you have in the active worksheet.

4. Count a Specific Word/Text String from a Range

Let’s think about a different situation.

Let’s say you need to count a specific word from a range of cells or to check the number of times a value appears in a column.

Here is an example.

Below you have a range of four cells. And, from this range, you need to count the count of occurrence of word “Monday”.

For this, the formula is:

=SUMPRODUCT((LEN(A1:A4) LEN(SUBSTITUTE(A1:A4,"Monday","")))/LEN("Monday"))

And, when you enter it, it returns the count of word “Monday”.

That’s 4.

Important Note

It returns the count of the word (word’s frequency) from the range not the count of the cells which have that word.

Monday is there four times in three cells.

Explanation

To understand this function, again you need to split it in four parts.

In the first part, LEN function returns and array of count of characters from the cells.

The second part returns an array of the count of character from the cells by removing the word “Monday”.

In the third part, LEN function returns the length of characters of wor word “Monday”.

After that, subtracts part one from part two and then divide it with part three, it returns an array with the count of word “Monday” from each cell.

In the fourth part, SUMPRODUCT retunrs sum of this array and give the count of “Monday” from the range.

Sample File

Download this sample file from here to learn more.

Conclusion

Whenever you are typing some text in a cell or a range of cells you can these methods to keep a check on the word count.

I wish someday in future Excel will get this option to count words. But, for the time being, you have all these awesome methods.

I hope this will help you to get better at Excel.

Now tell me one thing. Which method do you like the most? Please share with me in the comment section, I’d love to hear from you.

And, please don’t forget to share this tip with your friends.