How To Count A Specific Character In A Cell (Case Sensitive)

Example

=LEN(B3)-LEN(SUBSTITUTE(B3,C3,””))

Generic Formula

=LEN(Text)-LEN(SUBSTITUTE(Text,Character,””))

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!

About the Author

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.