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
=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.


51.1 - 806,158
Scroll down for Comments
0 0 votes
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
0
Would love your thoughts, please comment.x
()
x