Have you ever wanted to know how many specified characters are in a cell? You can do this with a fairly simple formula.

Required Functions

• LEN – LEN(str): Length of string
• SUBSTITUTE(str, new_str, old_str) – Replaces all specified string with another specified string

Create a string within a cell.

To find out how many “A” are in the text, as in the example above, use the following formula:

=LEN(B4)-LEN(SUBSTITUTE(B4,"","A")

What is happening in the Formula

Len(B4) is counting the total number of characters in the string, for this instance it is 6.

Substitute(B4,””,”A”) is replacing the text “A” in cell B1 with an empty string, thus removing the text from the string. We are then running the length function on the substituted text “BBCCC” thus returning 5.

The formula subtracts the substituted length from the original length of the string, returning the difference which is 1.