Aniruddah Alam
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
6 Useful Methods to Count Words in Excel
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 number of words in cell B5 which is 5 in this case.
🗝️ How Does the Formula Work? 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.. The LEN function calculates the total number of characters in cell B5 without the space. The TRIM function removes all spaces from text except for single spaces between words in cell B5. The LEN function calculates the total characters of the trimmed text in cell B5. 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.
Read More: How to Count Alphabet in Excel Sheet (5 Easy Methods)
2. Using 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.
Read More: How to Count Specific Words in Excel (3 Effective Ways)
3. Counting Specific Word 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” word 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? Here at first, with the help of the SUBSTITUTE function, we removed the word “yeah” from the cell B5. After that, we calculated the number of characters in cell C5 without the word “yeah” with LEN(SUBSTITUTE(B5, “yeah”,””). It calculates the total number of characters in cell B5 including the word “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. 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.
Read More: How to Show All Text in an Excel Cell (2 Easy Ways)
Similar Readings
- How to Create Fibonacci Calculator in Excel (With Easy Steps)
- Add Signature in Excel (3 Quick Ways)
- How to Perform Bootstrapping in Excel (with Easy Steps)
- Use of Task Pane in Excel (Detailed Analysis)
- How to Create Drill Down Chart in Excel (with Easy Steps)
4. Counting Specific Word 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 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.
Read More: How to Display Document Properties in Excel (3 Simple Methods)
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.
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.
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. Finally, please visit Exceldemy for more exciting articles on Excel.
Related Articles
- How to Insert WordArt in Excel (2 Simple Methods)
- Write a Paragraph in Excel Cell (5 Easy Ways)
- How to Create a Word Cloud in Excel (2 Easy Ways)
- Do Bookkeeping for Small Business in Excel (Step-by-Step)
- How to Search Text in Multiple Excel Files (2 Easy Ways)
- Create Double Entry Bookkeeping in Excel
- How to Overline Text in Excel (4 Easy Ways)
