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

This fomula will 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

This will count how many times the character ‘,’ (a comma) appears

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.


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.

56.1 - 832,916
Scroll down for Comments
0 0 votes
Article Rating
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x