How to Count Words in a cell?
The tutorial shows how to count words in Excel using the LEN function along with other Excel functions. It also gives formulas for counting words or text, whether case-sensitive or not, in a cell or range.
Microsoft Excel has several helpful functions for counting different things. The COUNT function counts cells with numbers, COUNTA counts non-blank cells, COUNTIF and COUNTIFS count cells based on conditions, and LEN calculates the length of a text string.
Unfortunately, Excel doesn’t have a built-in tool to count the number of words. But don’t worry! By combining several functions, you can create complex formulas to do almost anything. We will use this method to count words in Excel.
Table of Contents
How to count the total number of words in a cell
To count words in a cell, use the following combination of LEN, SUBSTITUTE and TRIM functions:
=LEN(TRIM(cell))-LEN(SUBSTITUTE(cell,” “,””))+1
Where cell is the address of the cell where you want to count words.
For example, to count words in cell A2, use this formula:
=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””))+1
And then, you can copy the formula down to count words in other cells of column A:
How this word counting formula works
First, use the SUBSTITUTE function to remove all spaces in the cell by replacing them with an empty text string (“”). This way, the LEN function will return the length of the string without spaces.
=LEN(SUBSTITUTE(A2,” “,””))
Next, subtract the length of the string without spaces from the total length of the string. Then, add 1 to the final word count, because the number of words in a cell is equal to the number of spaces plus 1.
Additionally, use the TRIM function to remove extra spaces in the cell. Sometimes a worksheet may have invisible spaces, like two or more spaces between words, or spaces at the beginning or end of the text. These extra spaces can mess up your word count. To prevent this, use the TRIM function to remove all unnecessary spaces, leaving only single spaces between words, before calculating the total length of the string
Improved formula that properly handles empty cells
The formula to count words in Excel is almost perfect, except for one problem—it returns 1 for empty cells.
To fix this, add an IF statement to check for blank cells.
=IF(A2=””, 0, LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””))+1)
As you can see in the screenshot above, the formula returns zero for blank cells, and the correct word count for non-empty cells
How to count specific words in a cell
To count how many times a specific word, text, or part of a text appears in a cell, use this formula:
=(LEN(cell)-LEN(SUBSTITUTE(cell, word,””)))/LEN(word)
For example, let’s calculate the number of “hungry” occurrences in cell A2:
=(LEN(A2)-LEN(SUBSTITUTE(A2, “hungry”,””)))/LEN(“hungry”)
Instead of typing the word you want to count directly into the formula, you can type it in a cell and reference that cell in your formula. This makes the formula more flexible and easier to use for counting words in Excel.
How this formula counts the occurrences of a specific text in a cell
- The SUBSTITUTE function removes the specified word from the original text. In this example, we remove the word entered in cell B1 from the original text in cell A2: Â SUBSTITUTE(A2, $B$1, “”).
- Then, the LEN function calculates the length of the text string after removing the specified word. In this example, LEN(SUBSTITUTE(A2, $B$1, “”)) returns the length of the text in cell A2 after removing all occurrences of the word specified in cell B1.
- After that, the above number is subtracted from the total length of the original text string:
(LEN(A2)-LEN(SUBSTITUTE(A2, $B$1,””)))
The result of this operation is the number of characters contained in all occurrences of the target word, which is 18 in this example (3 occurrences of the word “Hungry“, 6 characters each).
- Finally, the resulting number from the LEN function is divided by the length of the word. This means you divide the total number of characters in all occurrences of the target word by the number of characters in one occurrence of that word. In this example, if the total length after removing the word is 18 characters and the word itself is 6 characters long, dividing 18 by 6 gives us a result of 3.
Apart from counting the number of certain words in a cell, you can use this formula to count the occurrences of any text (substring). For example, you can count how many times the text “par” appears in cell A2:
Case-sensitive formula to count specific words in a cell
As you probably know, Excel’s SUBSTITUTE function is case-sensitive. This means that the word counting formula using SUBSTITUTE is also case-sensitive by default.
Case-insensitive formula to count specific words in a cell
If you need to count both uppercase and lowercase occurrences of a word, use either the UPPER or LOWER function inside SUBSTITUTE. This will convert both the original text and the word you want to count to the same case.
=(LEN(cell)-LEN(SUBSTITUTE(UPPER(cell),UPPER(text),””)))/LEN(text)
Or
=(LEN(cell)-LEN(SUBSTITUTE(LOWER(cell),LOWER(text),””)))/LEN(text)
For example, to count the number of occurrences of the word in B1 within cell A2 ignoring case, use this formula:
=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($B$1),””)))/LEN($B$1)
As shown in the screenshot below, the formula returns the same word count whether the word is typed in UPPERCASE (cell B1), lowercase (cell D1), or Sentence case (cell C1).
Count the total number of words in a range
To find out how many words are in a certain range, take the formula that counts the total words in a cell and use it inside either the SUMPRODUCT or SUM function.
=SUMPRODUCT(LEN(TRIM(range))-LEN(SUBSTITUTE(range,” “,””))+1)
Or
=SUM(LEN(TRIM(range))-LEN(SUBSTITUTE(range,” “,””))+1)
SUMPRODUCT is one of the few Excel functions that can handle arrays, and you complete the formula normally by pressing the Enter key.
For the SUM function to calculate arrays, you need to use it in an array formula. Complete this by pressing Ctrl+Shift+Enter instead of just Enter.
For example, to count all words in range A2:A4, use one of the following formulas:
=SUMPRODUCT(LEN(TRIM(A2:A4))-LEN(SUBSTITUTE(A2:A4,” “,””))+1)
=SUM(LEN(TRIM(A2:A4))-LEN(SUBSTITUTE(A2:A4,” “,””))+1)
Count specific words in a range
If you want to count how many times a specific word or text appears within a range of cells, use a similar approach. Take the formula to count specific words in a cell and combine it with the SUM or SUMPRODUCT function.
=SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range, word,””)))/LEN(word))
Or
=SUM((LEN(range)-LEN(SUBSTITUTE(range, word,””)))/LEN(word))
Remember to press Ctrl+Shift+Enter to correctly complete the array SUM formula.
For example, to count all occurrences of the word in cell C1 within the range A2:A4, use this formula:
=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4, C1,””)))/LEN(C1))
As you remember, SUBSTITUTE is a case-sensitive function, so the formula distinguishes between uppercase and lowercase text.
To make the formula case-insensitive, use either the UPPER or LOWER function.
=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE((UPPER(A2:A4)),UPPER(C1),””)))/LEN(C1))
Or
=SUMPRODUCT((LEN(A2:A4)-Â Â LEN(SUBSTITUTE((LOWER(A2:A4)),LOWER(C1),””)))/LEN(C1))
This is how you count words in Excel. To understand and possibly reverse-engineer the formulas.
