Formula to Count Occurrence of Specific Characters in a Cell

Formula that counts the occurrences of a specific character in a cell in Excel.

This tutorial includes a way to do a case sensitive and case insensitive count.

Sections:

Case Insensitive Count

Case Sensitive Count

More Examples

Notes

Case Sensitive Count

=LEN(A2)-LEN(SUBSTITUTE(A2,"i",""))

This formula counts the occurrence of a lower case i in cell A2. To count an upper case, just input the upper case version of the letter.

This is the default way to count a character in a cell and it is case sensitive; if you input a lower-case character, only the lower-case will be counted, and the same for upper case.

How it Works

(If you want, you can skip this part and just copy/paste the above formula and change the cell references to the cell that contains the text.)

The principal behind this formula is that you count how many characters are in a cell and then you perform a second count of the characters in the cell after you remove the character that you want to count, using the SUBSTITUTE function; then you substract the second number by the first one to get the final count.

LEN(A2) counts the characters in cell A2.

SUBSTITUTE(A2,"i","") replaces every instance of the letter i with nothing, an empty space "" and this effectively removes the i from the text. Once that is done, we then need to count how many characters remain after all the i characters have been removed.

LEN(SUBSTITUTE(A2,"i","")) the LEN() that surrounds the SUBSTITUTE function is what performs the second count.

Once both counts have been done, you simply subtract the count without the i from the count of all the characters in the cell and that's how we get the final formula:

LEN(A2)-LEN(SUBSTITUTE(A2,"i",""))

Case Insensitive Count

=LEN(A3)-LEN(SUBSTITUTE(LOWER(A3),"t",""))

This formula counts the occurrence of the letter t in cell A3, including any upper or lower case t characters.

Note: when using this function, you should only input lower-case letters. 

How it Works

This works exactly the same as the previous formula with the addition of the LOWER() function.

LOWER(A3) this function changes all the text to lower-case. This allows the SUBSTITUTE() function to work regardless of lower/upper-case text.

To understand the rest of the formula, look to the "How it Works" section for the previous example, as it covers in detail how the formula works.

More Examples

Count Upper Case

=LEN(A4)-LEN(SUBSTITUTE(A4,"T",""))

Count Lower Case

=LEN(A5)-LEN(SUBSTITUTE(A5,"t",""))

Count Spaces

=LEN(A6)-LEN(SUBSTITUTE(A6," ",""))

Notes

Once you understand the concept of how this formula works, you will see that it's not that difficult to use. The one part that is easy to forget is the SUBSTITUTE() function, so just make sure to practice using it.

Make sure to download the attached workbook so you can have all these examples in Excel.