How to Count Words in Excel (Formula)


- Written by Puneet

There is one option that I always wish Excel should have, and that is counting the number of words from a cell. If you work in MS Word there is an inbuilt option on the status bar which shows you how many words are there in the sheet.

there is an option in word to count words but not in excel

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.

As you know, in Excel, we have functions, and you can use them to calculate almost everything. You can create a formula that can count words from a cell.

Today in this post, you will learn how to count words in Excel from a cell, a range of cells, or even the entire worksheet. And I’ll also show you how to count a specific word from a range of cells.

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
formula to count word in excel using len substitute

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 to them. To understand this formula you need to split it into three parts.

how formula to count word in excel using len substitute works

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.

formula equation to count words in excel

The total number of characters with spaces and the total number of characters without spaces. And when you subtract both numbers get the number of spaces and in the end, you must 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.


count words in excel use if when cell is blank

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

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 VBA editor.
    add udf code to vbe to count words in excel cell
  2. 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.

udf to count word in excel using len substitute

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


formula to count word in excel from column row range

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 works but is just a small advance. The only difference is you have wrapped it in SUMPRODUCT and referred to the entire range instead of a single cell.

how sumproduct works to count word in excel

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.

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

vba code to count words in excel using vba

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 from 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 count of occurrences of the word “Monday”.

For this, the formula is:

count words in excel specific with sumproduct formula

And when you enter it, it returns to 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 function, 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.

use sumproduct formula to count a specific words from a range 1

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

use sumproduct formula to count a specific words from a range 2

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

use sumproduct formula to count a specific words from a range 3

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

use sumproduct formula to count a specific words from a range 4

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

Get the Excel File


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

Last Updated: March 24, 2024

19 thoughts on “How to Count Words in Excel (Formula)”

  1. To find out the count of specific word in an array I used SUM, it works.. Please tell me why you have used SUMPRODUCT?

  2. Hello, thank you for your post, but how to count specific words from another excel sheet ( formula is in Sheet1 but words is in Sheet2)

  3. Thanks a lot – this is great!
    To get a quote for a text translation (website text in excel file) I need the full word count per worksheet. Easy with your macro. 🙂

  4. In the solutions above all is ok, if there is always just ONE space between words. However, if anywhere in the strings, there is more than one space between words then the result will be wrong. Therefore, the best is to add in the UDF a loop to substitute all two subsequent spaces by one space and repeat it until there is no change in a string length (so there was no substitution).

  5. What if your cell contains the text “thisisagreatwebsite” (without the quotes, no capitals – all lowercase, and no spaces). How could I count those words?

  6. Thank you for the formula! It’s a great use case for the LEN function!
    To avoid erroneous results in case of “unnecessary” blanks I would suggest to add TRIM:
    LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,” “,””))+1
    Kind regards


Leave a Comment