Excel: How To Count How Many Times A Specific Character Appears In A Cell

Counting the Number of Times a Specific Character Appears In A Cell

This will count how many times the letter ‘a’ appears
=LEN(a1)-LEN(SUBSTITUTE(a1,”a“,””))

This will count how many times the character ‘,’ (a comma) appears
=LEN(a1)-LEN(SUBSTITUTE(a1,”,“,””))

How does this work?

LEN(a1) is the length (number of characters) of the content of cell A1

LEN(SUBSTITUTE(a1,”,“,””)) is divided into two parts

(SUBSTITUTE(a1,”,“,””)) removes the comma (or specified character) and then LEN counts the number of characters in the remaining value.

You take that number away from the first, and you have the number of characters (the COMMA in this example) removed.

eg:

Cell A1:  a,b,c

LEN(a1) = 5

(SUBSTITUTE(a1,”,“,””) would give a result of abc after the commas are removed.

The LEN(SUBSTITUTE(a1,”,“,””)) command then becomes LEN(abc) which equals 3 charcters in total

Therefore the final LEN(a1)-LEN(SUBSTITUTE(a1,”,“,””)) becomes:

LEN(a1) [FIVE] minus LEN(SUBSTITUTE(a1,”,“,””)) [THREE] BECOMES 5 -3 = 2

There are 2 commas in cell A1.

Leave a Reply to this Posting, but no links please.