How to Count Words in Excel (Formula)

1. Count Words from a Single Cell

To count words from a cell, you need to combine the LEN function with the 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 between 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. To understand this formula, you need to split it into three parts.

In the first part, you used the LEN function to count the number of characters from cell A1. And in the second and third parts, 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.

That’s the total number of characters with spaces and the total number of characters without spaces. When you subtract the second number from the first, you get the number of spaces, and in the end, you add one to 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 the 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 the word count if there is a value in the cell.

But both formulas above count every space, so extra spaces (two spaces between words, or spaces at the start or end) give you a wrong count. A cell with only spaces, or a formula that returns “”, isn’t blank for ISBLANK either. This version handles all of these by using TRIM, which removes the extra spaces before counting:

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

For example, for ” two spaces here ” (with extra spaces), the first formula returns 8, while this one returns the right count, 3. And for an empty cell or a cell with only spaces, it returns 0.

2. 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 that 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.

1. First of all, enter this code in a standard module in the VBA editor (Insert ➜ Module).
   
2. And then come back to your worksheet, enter “=MyWordCount(” and refer to the cell in which you have the value.

And it will return the word count. This function splits the text at every space, so like the first formula, it counts extra spaces as extra words. Save the file as a macro-enabled workbook (.xlsm) to keep the function.

Related: Formula Bar in Excel

3. 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 that you just need to use the same formula (with a simple change) which you have used above. And the formula will be:

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

In the above formula, A1:A11 is the range of cells, and when you enter the formula, it returns 77 in the result.

How It Works

This formula works in the same way as the first method, but with a small change. The only difference is you have wrapped it in SUMPRODUCT and referred to the entire range instead of a single cell.

Do you know 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.

There’s one catch: this formula adds 1 for every cell, so each blank cell in the range is counted as one word. If your range has blank cells or extra spaces, use this version, which counts only the cells with text and ignores the extra spaces:

=SUMPRODUCT((LEN(TRIM(A1:A11))>0)*(LEN(TRIM(A1:A11))-LEN(SUBSTITUTE(A1:A11," ",""))+1))

4. Word Count from the Entire Worksheet

This code is one of the useful macro codes which I use in my work, and it can help you to count all the words in a worksheet. It trims each cell first, so extra spaces don’t add to the count.

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.

Related: What is VBA in Excel

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

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

Take this example: Below you have a range of four cells, and from this range, you need to count the occurrences of the word “Monday”.

For this, the formula is:

=SUMPRODUCT((LEN(D6:D9)-LEN(SUBSTITUTE(D6:D9,"Monday","")))/LEN("Monday"))

And when you enter it, it returns the count of the word “Monday”. That’s 4.

Important: 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.

How It Works

To understand this formula, again you need to split it into four parts. In the first part, the LEN function returns an array of the count of characters from the cells.

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

In the third part, the LEN function returns the number of characters in the word “Monday”.

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

In the fourth part, SUMPRODUCT returns the sum of this array and gives the count of “Monday” in the range.

Keep in mind that SUBSTITUTE is case-sensitive, so this formula doesn’t count “monday” in lowercase. It also counts the word when it’s part of a longer word, like “Mondays”. To count the word in any case, convert the text to lowercase with LOWER and use the word in lowercase:

=SUMPRODUCT((LEN(D6:D9)-LEN(SUBSTITUTE(LOWER(D6:D9),"monday","")))/LEN("monday"))

Count Words with TEXTSPLIT in Microsoft 365

If you use Microsoft 365, you can also split the text into words with TEXTSPLIT and count them with COUNTA. TRIM removes the extra spaces first, and the IF part returns 0 for a blank cell:

=IF(TRIM(A1)="",0,COUNTA(TEXTSPLIT(TRIM(A1)," ")))

Conclusion

Whenever you are typing some text in a cell or a range of cells, you can use these methods to keep a check on the word count. I wish someday in the future Excel will get this option to count words. But, for the time being, you have all these awesome methods.

Which method do you like the most? Make sure to share your views with me in the comment section, I’d love to hear from you. And please, don’t forget to share this post with your friends, I am sure they will appreciate it.

Frequently Asked Questions

=COUNTIF(D6:D9,"*Monday*")