In this guide, we're going to show you how to count a specific character in a string in Excel. Finding the number of a specific character, such as a delimiter, can help with splitting a text with the use of formulas.
Syntax
How it works
The formula can create a new string by removing the character you want to count, and to subtract the character count of the new string.
You can create a string without the character you want to count by replacing it with an empty character (“”). To do this, you can use the SUBSTITUTE function.
You need to provide the original text, the character you want to count and an empty string (“”) as arguments. The rest can be handled with the LEN function which gives the character count of the given text.
Examples to count a specific character
In the following example, the formula counts the characters under column C, within the text values in the column B.
The formula returns 1 for “b” character count in “Bulbasaur” whereas there are two characters. Since the SUBSTITUTE function is case sensitive, it replaces the characters that match case. Use this version when you need to count a specific character in a string by its case.
Not case sensitive count
If you would like to count character regardless of its case, use either the UPPER or the LOWER function based on the character you are searching. Either of these functions can convert the case of all characters in the original string. Thus, you can search without case differences.
=LEN(B14)-LEN(SUBSTITUTE(UPPER(B14),C14,""))
Tip: Prefer the UPPER function instead if the character you want to count is in uppercase, or vice versa.
