Updated August 21, 2023
Excel Word Count (Table of Contents)
Introduction to Count Words in Excel
In this topic will learn how to Count Words in Excel, the specific or total number of words in a cell or a cell range in Excel by using formulas.
Unlike MS Office, where we can easily count the number of words, MS Excel doesn’t have a built-in function. However, we can count words in Excel by combining various functions to make a formula.
We will use Excel functions like TRIM, LEN, and SUBSTITUTE to make a formula yielding words.
Let me give you the overview of these functions one by one –
- Trim: This function removes duplicate or extra spaces from a cell or a cell range. The spaces could even be found in a cell before or after the word.
- Len: This function returns the length of a string in a cell or a cell range. It includes spaces as well.
- Substitute: This function replaces anything in a cell with any text, number, etc., of our choice. In our case, this function will replace spaces with blanks.
We will now look into different scenarios of word count with examples in Excel. We will also learn how to write syntax by combining the above three functions to get the word count in Excel.
How to Count Words in Excel by using formulas?
Counting words in Excel are very simple and easy. Let’s understand the working of counting words in Excel using formulas with its examples.
Example #1 – Count the Total words in a cell
To get the count of words in a cell, we will use the below syntax:
=LEN(TRIM(cell))-LEN(SUBSTITUTE(cell,” “,””))+1
There is a text string in cell A2, and we want to count the total number of words. So it will be shown in cell B2.
We will first use the function Trim as below:
Press Enter.
Now, we get the text without any extra spaces. Sometimes a worksheet could have a duplicate or more than one space between words or spaces at the start or end of the text. The trim function removes all those extra spaces. Using this function as a caution to get the correct result is good.
We will calculate the length of the string now, as shown below:
Press Enter to get the length of the string without any space.
Now, we will use a SUBSTITUTE function to replace the old text with the new one in the string. Here we will replace the spaces ” with blanks. ”
Press Enter, and now we see the string without any spaces.
Now to get the length of the string without spaces, we will use the LEN function as shown below:
Press Enter to get the length of the string without spaces.
Finally, to get the count of words in the string, we will subtract the length of the string with spaces (40) from the length of the string without spaces (35) and by adding 1 to it. Adding 1 will give us the total number of words, as spaces are one less than words.
Press Enter, and you will see the exact count of words in the string in cell A2.
This is how we will use the formula to count the total number of words in Excel. In case we have multiple strings for word count in Excel, as shown below.
We just need to drag cell B2 downwards, and the formula will be applied for all the strings.
If any of the above cells in the range A2:A5 has no text or is blank, the formula would return the word count as 1 instead of 0 for that specific cell. This scenario is shown below:
We can modify our formula by adding the IF function to avoid such incorrect results, making it conditional. For example, we will use IF to give a condition to the cell that if it is blank, it should return the word count as 0.
The modified formula will be like this:
=IF(cell = ““,0,LEN(TRIM(cell))-LEN(SUBSTITUTE(cell,” “,””))+1)
The above data, it will be applied in the below manner.
Drag cell B2 downwards; the formula will be applied for all the strings.
Example #2 – Count the Total words in a cell
We have a long sentence to count the number of words in it.
First, we will get the length of cell A2, which has no extra space. Then we will substitute the spaces in the sentence with the blanks and get its length. Finally, we will subtract the total length of the sentence from the length of the sentence without spaces. Also, we will add 1 to get the final word count. This is because, in a cell, the number of words will be several spaces +1.
Press Enter to see the result.
I hope you have now understood the formula and will be able to write. It is tricky, but writing this 2 or 3 times will give you more clarity.
Example #3 – Count Specific words in a cell
How can we count how many times a specific name or text has appeared in a string? We can do it by the below formula:
=(LEN(Cell)-LEN(SUBSTITUTE(CELL,word,””)))/LEN(word)
There is a text string in cell A2, and we want to count the specific words in a cell. It will be shown in cell B2.
Let’s say we want to know how often the word “home” has occurred in the above sentence. We can know this by the below formula:
Press Enter to see the result.
We will first calculate the length of the original text (A2). Now the SUBSTITUTE function will completely replace the word “home” in the sentence with blanks. With the LEN function, we will get the length of text A2 with the original “home” word. After that, we will subtract the length of this new text string from the length of the original text string. We will subtract 102-90, and that will give us 12.
And lastly, we will divide the above number by the length of the word “home”,4. This will give us the result 3. The word “home” has occurred 3 times in the above string.
Also, we can use a cell reference to the word in place, “home”.
Example #4 – Count the total number of words in a Range
In case we want to know the total number of words in a range, we can do so as below:
We will include a Sum function to calculate the total number of words. For the Sum function, to calculate the array, we will press Ctrl+Shift+Enter instead of normal Enter.
Example #5 – Count the total number of a specific word in a Range
If we want to know the total number of occurrences of a specific word in a range, we can do it with the below formula:
In this example, we want to know the total number of times the word “London” occurred in the below range A2:A4.
We can know this with the below formula:
Once we have written the formula, we will press CTRL+SHIFT+Enter to get the result, as the Sum function will calculate the array. Once we press the above keys, we will get the below result.
In cell range A2:A4, the word “London” has occurred 3 times. Also, you can use a cell reference in place of the word “London”.
Things to Remember About Count Word in Excel
- As a practice, use the TRIM function in writing the formula, as it will eliminate the chances of error if the cell or range has extra spaces.
- Whenever you want to know the count of a specific text in a cell or range, always write that word in double quotes in the formula like “word”.
- SUBSTITUTE is case sensitive, so be careful when writing the word you want to substitute. It should be in the same case as the original text, or it won’t be substituted.
- Always press Ctrl+Shift+Enter to calculate the array SUM formula.
Recommended Articles
This has been a guide to Count Word in Excel. Here we discuss how to count words in Excel using formulas, examples, and a downloadable Excel template. You may also look at these suggested articles –
