Excel Word Count (Table of Contents)
Count Words in Excel
In this topic will learn how to count a 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 for this. However, we can count words in Excel by combining various functions to make a formula for the same.
We will use excel functions like TRIM, LEN and SUBSTITUTE to make a formula which will yield the count of words.
Let me give you the overview of these function one by one –
- Trim: This function removes duplicate or extra spaces from a cell or a cell range. The spaces could even be found before or after the word in a cell.
- 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 by 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 combining the above three functions to get the word count in excel.
How to Count Words in Excel by using formulas?
Count words in excel are very simple and easy. Let’s understand the working of count words in excel using formulas with its examples.
4.9 (2,180 ratings)
View Course
Example #1
Count 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. 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 duplicate or more than one spaces between words or spaces at the start or end of the text. The trim function removes all those extra spaces. It is good to use this function as a caution to get the correct result.
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 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 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) with 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 the cell B2 downwards and the formula will be applied for all the strings.
In case, any of the above cells in the range A2:A5 has no text or is blank, the formula would then return word count as 1 instead of 0 for that specific cell. This scenario is shown below:
To avoid any such incorrect results, we can modify our formula by adding IF function making it conditional. We will use IF to give condition to the cell, that if it is blank, it should return the word count as 0.
The modified formula will be like:
=IF(cell = ““,0,LEN(TRIM(cell))-LEN(SUBSTITUTE(cell,” “,””))+1)
On the above data, it will be applied in the below manner.
Drag the cell B2 downwards and the formula will be applied for all the strings.
Example #2
Count Total words in a cell
We have a long sentence to count the numbers 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 with the length of the sentence without spaces. Also, we will add 1 to get the final word count. This is because, in a cell, a number of words will be number of space +1.
Press Enter to see the result.
Hope, you have now understood the formula and will be able to write. It is a bit 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 as to how many times a specific name or a text has appeared in a string? We can do it by the below formula:
=(LEN(Cell)-LEN(SUBSTITUTE(CELL,word,””)))/LEN(word)
There 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 many times 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 LEN function we will get the length of text A2 with the original word that is “home”. After that, we will subtract the length of this new text string with 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 with the length of 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 total number of words in a Range
In case we want to the total number of words in a range, we can do so as below:
To calculate the total number of words, we will include a Sum function. For 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 occurrence 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 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 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 in writing the word that you want to substitute. It should be in the same case as it is in the original text, else 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 by using formulas along with excel examples and downloadable excel template. You may also look at these suggested articles –
