In this post you will learn how to count the number of times a specific character occurs in a text string in Excel.
Counting specific characters in a cell –
A useful trick is to be able to count the number of times a specific character exists in a text
string. The technique for doing this in Excel is relatively clever. If you wanted to count the
number of times the letter s appears in the word Mississippi, for example, you could count
them by hand, of course, but systematically, you could follow these general steps:
- Measure the character length of the word Mississippi (11 characters).
- Measure the character length after removing every letter s (7 characters).
- Subtract the adjusted length from the original length.
After performing these steps, you’ll accurately conclude that the letter s occurs four times
in the word Mississippi.
A real-world use for this technique of counting specific characters is to calculate a word
count in Excel. In Figure below, you’ll see the following formula used to count the number of
words entered in cell B4 (nine words in this case):
=LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1
The formula first uses the LEN function to measure the length of the text in cell B4:
LEN(B4)It then uses the SUBSTITUTE function to remove the spaces from the text:
SUBSTITUTE(B4," ","")Wrapping the SUBSTITUTE function in a LEN function gives us the length of the text
without the spaces. Note that we have to add one (+1) to that answer to account for the
fact that the last word will not have an associated space:
LEN(SUBSTITUTE(B4," ",""))+1Subtracting the adjusted length from the original length gives us our word count:
=LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1