If there’s a word limit for Excel data entry, you would definitely want to know the total word count during the process. Or, maybe you might just want to know the occurrences of a specific word to remove the duplicates.
Although, Excel lacks a dedicated features to count words of your data, you can still use the custom formula to achieve this.
The best part is you can construct the formula to count words within a cell or a range, case-sensitive or insensitive words. So, yes in Excel, formulas can be your Knight in shining armor every time.
Count All Words
To count the total number of words in Excel, we will create a custom formula. Here, we will use the LEN, TRIM, and SUBSTITUTE functions nested together. Let’s take a look at the example of counting all words in a cell and range.
Example 1: Within a Cell
To know the total word count in a cell, here’s the formula. You can also use this formula for sentences having irregular spaces or delimiters like commas, etc.
Formula: LEN(TRIM(cell))-LEN(SUBSTITUTE(cell, " ", ""))+1In cell B2, we entered this formula to count all words
=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2, " ", ""))+1
We got 8 in the first cell. After that, we extended the formula for other cells.
Now, let us break down the formula to see how it counted and returned the total number.
- SUBSTITUTE(A2, ” “, “”): Firstly, the substitute function replaces all the spaces in between the sentence of cell A2 with a null string. So, now my word would be Let’scountthetotalwordsofthiscell.
- LEN(SUBSTITUTE(A2, ” “, “”)): The LEN function counts the total number of the text string in cell A2 and returns 33.
- LEN(TRIM(A2)): Here, the TRIM function keeps only the single spaces and removes any unwanted spaces. Then, the LEN function counts the total length which is 40.
- LEN(TRIM(A2))-LEN(SUBSTITUTE(A2, ” “, “”))+1: Finally, this formula calculates (40-33) + 1. Then, returns the overall word count.
Example 2: Within Range
The formula to know the total number of words in a range is
=SUMPRODUCT(LEN(TRIM(cell range))-LEN(SUBSTITUTE(cell range, " ", ""))+1)Since the SUMPRODUCT function supports an array formula, you can simply press enter after typing the formula.
Suppose, I want to calculate the total number of words from cell range A1 through A10. To do so, I entered the formula as
=SUMPRODUCT(LEN(TRIM(A1:A10))-LEN(SUBSTITUTE(A1:A10, " ", "")) +1)
The formula returned 24 in the cell B11. Let’s find out how we got this result.
- LEN(SUBSTITUTE(A1:A10, ” “, “”)): Here, we have passed down the argument in the SUBSTITUTE function to replace all spaces from the words with a null string. Then, the LEN function counts the total text characters from the returned value.
- SUMPRODUCT(LEN(TRIM(A1:A10)): In this formula, the TRIM function eliminates the unwanted spaces from the words. Then, the LEN function counts and returns the total number of text strings from the A1 through A10 cell ranges. Finally, the SUMPRODUCT function calculates the sum of the product of the array and returns the total word count.
Count Specific Words
You can also choose to selectively count only the specific word in Excel. For example, count only the word “Excel” from a cell or cell range.
Note that the formula for Example 1 and 2 is case-sensitive. This means that the formula regards “nisha”, “Nisha”, and “NISHA” differently.
Also, if you’re passing down the actual “Word” in the formula, make sure to double-quote them. If you don’t do this, Excel will eventually send you a #NAME? cell error.
Example 1: Within a Cell
To count the number of specific words in a cell range, the formula is
=(LEN(cell) - LEN(SUBSTITUTE(cell, word, "")))/LEN(word)Example: From the value of cell A5, let us find out the total number of “Marketing” word. For this, we entered the formula as
=(LEN(A5)- LEN(SUBSTITUTE(A5, "Marketing", "")))/LEN("Marketing")
We got 5 as a result. Now, let us look into the formula breakdown to see how it worked.
- (LEN(A5) – LEN(SUBSTITUTE(A5, “Marketing”, “”))): Here, the SUBSTITUTE function replaces all the spaces from the sentence of A5 with a null. Then, the LEN function counts that sentence. LEN(A5) subtracts the result and returns a number.
- LEN(“Marketing”): The LEN function counts the total text string number in the word Marketing.
- (LEN(A5)- LEN(SUBSTITUTE(A5, “Marketing”, “”)))/LEN(“Marketing”): Finally, the values are divided. Then, the formula returns the total occurrence of the specified word.
Example 2: Within a Range
Just like above we will be subtracting the text strings and dividing at the end to count the total number of certain words. But, this time in order to return an array, we will use the SUMPRODUCT function.
=SUMPRODUCT(LEN(cell range)-LEN(SUBSTITUTE(cell range, word, "")))/LEN(word)Again, let us calculate the word “Marketing” from the given range. To do so, I will enter the formula as
=SUMPRODUCT(LEN(B4:B6)-LEN(SUBSTITUTE(B4:B6,"Marketing", "")))/LEN("Marketing")
The formula returned 13.
Example 3: Case Insensitive
The formula to count specific text in the above examples is case-sensitive. So, now let us look into the case-insensitive formula. You can use any one formula mentioned in the box.
=(LEN(cell)-LEN(SUBSTITUTE(UPPER(cell), UPPER(word), "")))/LEN(word)
OR,
=(LEN(cell)-LEN(SUBSTITUTE(LOWER(cell),LOWER(word), "")))/LEN(word)To demonstrate an example, I will use the first formula. Here, I will be counting the word “WOOD” from the cell C3. I entered the formula as
=(LEN(A2)- LEN(SUBSTITUTE(UPPER(A2), UPPER($B$1), "")))/LEN($B$1)
Even though the word “wood” is in uppercase and lowercase in cell C3, this formula counted all of them and returned 4 as an output.