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.
62.1 - 870,680