In this article, I will show you how to count words in Excel. While working in Excel, we may want to calculate the total number of words in a cell or in a range. Moreover, we may need to count a specific word (both in case sensitive and case insensitive) in an Excel sheet. Here, I will cover all of those issues. So, let’s get started.

**Table of Contents**Expand

**How to Count Words in Excel: 6 Useful Methods**

In this section, we will demonstrate 6 effective methods to** count words in Excel** with appropriate illustrations. Now, for illustration purposes, I have taken a data set containing a set of strings (from the famous poem **Mary had a little lamb)**. And, our target is to count the number of words in each cell and in the whole range. (see the figure below)

We will go through all those methods below one by one to count the words according to our needs. Let’s begin with our first method where we will use three Excel functions to **count words **in a cell.

### 1. Use of LEN, TRIM & SUBSTITUTE Functions to Count Words in a Cell

In this method, we will use a complex formula with a combination of **LEN**, **TRIM**, and **SUBSTITUTE **functions to count words in a cell. To know more, follow the steps below to know more.

**Steps:**

- In cell
**C5**, write down the following formula.

`=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1`

- Now, press
**Enter**. You will see that this will result in a number of words in cell B5 which is 5 in this case.

**🗝️ How Does the Formula Work?**

**SUBSTITUTE(B5,” “,””)**

The **SUBSTITUTE **function substitutes the spaces of cell **B5 **with no spaces. In other words, it removes all the spaces from the words in cell **B5**..

**LEN(SUBSTITUTE(B5,” “,””))**

The **LEN **function calculates the total number of characters in cell B5 without the space.

**TRIM(B5)**

The **TRIM **function removes all spaces from text except for single spaces between words in cell **B5**.

**LEN(TRIM(B5))**

The **LEN **function calculates the total characters of the** trimmed text** in cell **B5.**

**LEN(TRIM(B5))-LEN(SUBSTITUTE(B5,” “,””))+1**

Here, total number of characters in cell **B5 **without spaces is subtracted from total number of characters with spaces. Hence, essentially we get total number of spaces in cell **B5**. As we know, for one space, we get two words in a sentence. Therefore, we add an extra 1 to the formula to get the final word count.

- Now, use the
**Fill Handle**to get the word counting for the rest of the cells.

- Unfortunately, this formula has a major drawback. Even if a cell is empty, it counts as 1.

- Now, to overcome this problem and properly handle the empty cells, we add an
**IF**function before the formula like this below.

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

In this way, we can** count words **from any cell whether it is empty or not.

### 2. Using the SUM Function to Count Words in a Range

In this method, we will **count words** in a range. This time, we will also work with the previous data set.

Here, we will count the total words in the range of **B4:B9** by combining **the SUM function** with the previously used formula in method 1**. **To do that, follow the steps below.

**Steps:**

- In cell
**C10**, write down the following formula and press**Enter**.

`=SUM(IF(B5:B9="",0,LEN(TRIM(B5:B9))-LEN(SUBSTITUTE(B5:B9," ",""))+1))`

- As a result, you should see the total number of words in the cell.

**🗝️ How Does the Formula Work?**

This formula works exactly as described in method 1 with some minor exceptions. Here, in the argument of functions, we gave a range instead of a cell. Additionally, we have incorporated the **SUM **function to add up all those results from the cells of range.

**Note**: If you don’t have the latest version of Excel, you may find errors while applying this formula. Hence, you have to use** the SUMPRODUCT function** instead of the **SUM **function and press **Ctrl+Shift+Enter** to get a proper result.

### 3. Counting Specific Words in a Cell

In this method, we will count a specific word in a cell. For illustration, I have taken the entire poem of “**Mary Had a Little Lamb**” in a single cell. Here, I will try to find out how many “**yeah**” words are there in this cell.

To count this specific word in cell **B5**, follow the steps below.

**Steps:**

- In cell
**C5**, write down the following formula and press**Enter**.

`=(LEN(B5)-LEN(SUBSTITUTE(B5, "yeah","")))/LEN("yeah")`

- As a result, you will have the following result as expected.

**🗝️ How Does the Formula Work?**

**SUBSTITUTE(B5, “yeah”,””)**

Here at first, with the help of the **SUBSTITUTE **function, we removed the word “yeah” from the cell **B5**.

**LEN(SUBSTITUTE(B5, “yeah”,””)**

After that, we calculated the number of characters in cell **C5** without the word “yeah” with **LEN(SUBSTITUTE(B5, “yeah”,””)**.

**LEN(B5)**

It calculates the total number of characters in cell **B5 **including the word “yeah”.

**LEN(B5)-LEN(SUBSTITUTE(B5, “yeah”,””)**

** **Here, we have subtracted the total number of characters of **B5 **without the word **yeah **from the total number of characters of **B5**. So, we get the total number of characters of the existing instances of the word “yeah” in cell **B5**.

**(LEN(B5)-LEN(SUBSTITUTE(B5, “yeah”,””)))/LEN(“yeah”)**

Here, we divide the previous result with the character length of a single “**yeah**” word to get the number of occurrences of the word in cell **B5**.

### 4. Counting Specific Words in a Range

In this method, we will see how we can count a specific word in a range in Excel. For illustration, I have taken the previously used data set.

Now, I will try to count the occurrence of the word, “**Mary**” throughout the range (**B5:B20**). In this case, we will do exactly what we have done in method 2 and method 1. Here, we will only replace the cell reference with the range reference in the formula and add all the results for each cell with the **SUM **function. So let’s do this.

**Steps:**

- In cell
**D5**, write down the following formula and click**OK**.

`=SUM((LEN(B5:B20)-LEN(SUBSTITUTE(B5:B20, "Mary","")))/LEN("Mary"))`

- As a result, you will see the number of times the word “
**Mary**” appeared in the range**B5:B20**.

**Note**:

- If you don’t have the latest version of Excel, you may find errors while applying this formula. Hence, you have to use
**the SUMPRODUCT function**instead of the**SUM**function and press**Ctrl+Shift+Enter**to get a proper result. - The
**SUBSTITUTE**function is a case-sensitive function. Hence, if you need to count both the Upper Case and Lower Case of the word in a range (or in a cell in method 1), you should use the**UPPER**or**the LOWER function**inside the arguments of the**SUBSTITUTE**function like this.

`=SUM((LEN(B5:B20)-LEN(SUBSTITUTE(UPPER(B5:B20), UPPER("Mary"),"")))/LEN("Mary"))`

Or

`SUM((LEN(B5:B20)-LEN(SUBSTITUTE(LOWER(B5:B20), LOWER("Mary"),"")))/LEN("Mary"))`

### 5. Use of Microsoft Word to Count the Total Words in Excel

Unlike **Microsoft Word**,** Microsoft Excel** doesn’t provide any built-in tool to calculate the total number of words. Hence, if we want to quickly determine the number of words an Excel data sheet contains, we can copy the data from the Excel sheet and paste it into Microsoft Word to instantly determine the number of words. To know more, follow the steps below.

**Steps:**

- At first, copy the cell or range data from where you want to
**count words.**

- Now, paste the copied cell into a new file of MS
**Word**.

- Below the status bar, you will see the total word count.

In this way, we can use the MS Word program to **count words**.

### 6. Creating User-Defined Function to Count Words

finally, we can create our own **function in VBA** code to **count words** in Excel. Fortunately, in Excel, we can create a user-defined function to perform our required task. To know more, follow the steps below.

**Steps:**

- First, open the Visual Basic window by pressing
**Alt+F11.** - Then, open a new Module by going to
**Insert**>>**Module**.

- Now, write the following code in the module and save it.

```
Function WORD_COUNT(rnge As Range)
WORDCOUNT = 0
For Each cll In rnge
If cll.Value = "" Then
This_Count = 0
Else
This_Count = Len(Trim(cll.Value)) - Len(Replace(cll.Value, " ", "")) + 1
End If
WORDCOUNT = WORDCOUNT + This_Count
Next
WORD_COUNT = WORDCOUNT
End Function
```

**🗝️ How Does the Code Work?**

Here we have created a user-defined function named **WORD_COUNT**. Its argument **rnge **is range type. Then, we used the **For Loop **to **count words** in each cell. Before that, we checked whether the cell is empty or not by the **IF **function and set the word count (**This_Count**) to 0 for the empty cells. On the contrary, for the non-blank cell, we calculate the word count. And then we summed the word count for each cell as **WORDCOUNT**. Finally, the value of **WORDCOUNT **was set as the returning value of the **WORD_COUNT **function.

- Now, go to the worksheet and use the
**WORD_COUNT**function to count the words in your desired range( or cells).

`=WORD_COUNT(B5:B23)`

Though I have shown the application of this function for a range, you can use it for a cell as well.

**Read More:** Excel VBA to Count Occurrences in String

**Things to Remember**

- If you find an error applying range in a function, use an alternative one (the
**SUMPRODUCT**function for the**SUM**function) and press**Ctrl+Shift+Enter**instead of**Enter**.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

That is the end of this article regarding how to count words in Excel. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.

## Knowledge Hub

- How to Count Words in Excel Column
- How to Count Specific Words in a Column in Excel
- How to Count Specific Names in Excel
- How to Count Words Separated by Comma in Excel Cell

**<< Go Back to Learn Excel**