Learn how to count a word in Excel in a few simple steps. Excel is a great tool for working with all types of data, including text.
While Excel does not have a built-in formula for counting a word, you can easily use a few simple formulas to count a specific word in Excel or count unique text in Excel.
Table of Contents
Count a word in Excel
To count the number of times a specific word appears in a cell, you can use the functions LEN and SUBSTITUTE.
Formula
=(LEN(text)-LEN(SUBSTITUTE(text, word, “”)))/LEN(word)
Arguments (inputs)
text = the text is the cell containing the text to be searched for the word to be counted
word = the word to be counted
Example
In the example above the formula uses the LEN and SUBSTITUTE functions to determine how many times the word appears in the text.
In the example above the LEN function selects a defined number of characters from the right hand side of the text in cell B6.
=(LEN(B6)-LEN(SUBSTITUTE(B6,D6,””)))/LEN(D6)
The first LEN function counts the number of characters of the text. In this case there are 539 characters in the given text.
The LEN(SUBSTITUTE()) combination replaces the word (“sun”) with blank (“”), and returns the number of characters, in this case returning 533.
The formula subtracts 533 from 539, and returns 6. It then divides by the number of characters in the word (“sun”), 3. The result of 6 / 3 is 2, so there are 2 instances of the word “sun” in the text.
Note, the SUBSTITUTE formula is case sensitive. The next section
Count a word in any case in Excel
Because the SUBSTITUTE function is case sensitive, a minor modification needs to be made to the formula to count the number of words if the case of any character changes. We can begin by using the same formula as above using the LEN and SUBSTITUTE functions, and adding the LOWER function.
Formula
=(LEN(text)-LEN(SUBSTITUTE(LOWER(text), LOWER(word), “”)))/LEN(word)
Arguments (inputs)
text = the text is the cell containing the text to be searched for the word to be counted
word = the word to be counted
Example
In the example above the formula uses the LEN, SUBSTITUTE, and LOWER functions to determine how many times the word appears in the text.
In the example above the LEN function selects a defined number of characters from the right hand side of the text in cell B6.
=(LEN(B6)-LEN(SUBSTITUTE(LOWER(B6),LOWER(D6),””)))/LEN(D6)
The first LEN function counts the number of characters of the text. In this case there are 539 characters in the given text.
The LEN(SUBSTITUTE()) combination replaces the word (“the”) with blank (“”), and returns the number of characters, in this case returning 497. The LOWER function simply changes all of the characters of the text and the word to lower case characters so they can be compared.
The formula subtracts 497 from 539, and returns 42. It then divides by the number of characters in the word (“the”), 3. The result of 42 / 3 is 14, so there are 14 instances of the word “the” (in any case) in the text.
Using the original formula without LOWER, would have resulted in 7 instances of the word “the”.
