How to Count Words in Excel?

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.

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.

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

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

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.

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

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.

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

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.

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

Or

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

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

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