How to Count the Occurrences of Characters in Excel?

Spread the love

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," ",""))+1

Subtracting the adjusted length from the original length gives us our word count:

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

Rating: 1 out of 5.

Leave a Reply