# How to COUNT Words in Excel

There is one option which I always wish Excel should have and that's count 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.

## Four Different ways to Count Words in Excel

Now without any ado, let's get started.

### 1. The 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. 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.

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

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.

### 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 (just 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.

#### here's how it works

This formula works in the same way as the 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.

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

### 3. Word Count from Entire Worksheet with VBA Code

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

### 4. 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 to 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 occurrence of the 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

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.

#### ...let me explain how it works

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

In the first part, LEN function returns an array of the 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 the word “Monday” from each cell.

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

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

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.

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

### 16 thoughts

1. Thank you Sir,

Thank you very much!

4. Thank You Sir, My excel problem is solved.

5. Thank you, i like it.

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

7. Thank you, just used it and it worked well.

8. i like len and substitute

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

• Even we can use TRIM on a lower note.