Example

Generic Formula

• Text – This is the text string in which you want to count the occurrences of a given single character.
• Character – This is the character which you want to count.

What It Does

This formula will count the number of occurrences of a given single character within a text string in a single cell. This formula is case sensitive and will consider upper and lower case letters to be different characters.

How It Works

The formula takes the difference between these two items:

• Character count of the full text string. This is given by the LEN(Text) part of the formula.
• Character count of the text string with all the characters we’re trying to count removed. This is given by the LEN(SUBSTITUTE(Text,Character,””)) part of the formula.

In our example LEN(“Apples and bananas”) results in 18 since there are 18 characters in the string.

In our example SUBSTITUTE(“Apples and bananas”,”a”,””) results in the text string “Apples nd bnns”, where each occurrence of “a” has been removed. The character count of this new text string is then found by LEN(“Apples nd bnns”) and this results in 14 since there are 14 characters in this string.

The difference of LEN(Text)-LEN(SUBSTITUTE(Text,Character,””)) is just the count of all our removed characters from the text string. In our example this is 18-14, so there are 4 “a” characters in the text string!