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…

ai2

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“,””))


These Adverts help to pay for this site.

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.


Agoda HolidaysAgoda HolidaysThese Adverts help to pay for this site.

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.


11.1 - 546,870
DreamHost

Scroll down for Comments
0 0 vote
Article Rating
Subscribe
Notify of

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

0 Comments
Inline Feedbacks
View all comments