Formula 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 a bit clever. To figure out, for example, how many times the letter s appears in the word Mississippi, you can count them by hand, of course, but systematically, you can follow these general steps:

1. Measure the character length of the word Mississippi (11 characters).
2. Measure the character length after removing every letter s (6 characters).
3. Subtract the adjusted length from the original length.

You can then accurately conclude that the number of times the letter s appears in the word Mississippi is four.

A real-world use for this technique of counting specific characters is to calculate a word count in Excel. Image 3-8 shows the following formula being used to count the number of words entered in cell B4:

=LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1

Image 3-8: Calculating the number of words in a cell.

How it works

This formula essentially follows the steps mentioned earlier in this section. The formula uses the LEN function to first 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 that SUBSTITUTE function in a LEN function gives you the length of the text without the spaces. Note that you have to add one to that answer to account for the fact that the last word doesn’t have an associated space.

LEN(SUBSTITUTE(B4," ",""))+1

Subtracting the original length with the adjusted length gives you the word count.

=LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1

Categories: Formula