Count instances of a specific character in a string

 Problem: How do I count the number of instances of a specific character in a string?

Answer: Use this formula:

=LEN(LOWER(D3))-LEN(SUBSTITUTE(LOWER(D3),D4,""))

where D3 is the string to be searched on, and D4 is the specific character you want to search

This example is not case sensitive, so if you want a formula that is not case sensitive, then change the formula to =LEN(D3)-LEN(SUBSTITUTE(D3,D4,""))

Explanation: The SUBSTITUTE function: SUBSTITUTE(LOWER(D3),D4,"")

removes all of the characters being counted in the original string. The length of this new string without the specific character is then subtracted from the length of the original string: LEN(LOWER(D3))-LEN(SUBSTITUTE(LOWER(D3),D4,"")), giving you the total instances of the specific character. You can use either UPPER or LOWER function, it does not really matter. This formula is not case sensitive so it will search for either "a" or "A". To make the formula case sensitive simply remove the LOWER or UPPER function in the formula, like this: LEN(D3)-LEN(SUBSTITUTE(D3,D4,"")).