Excel cell - how to count number of occurances of a character

By Santhosh N

Microsoft Office Excel provides couple of functions (LEN and SUBSTITUTE) to count the number of occurances of character in a cell.

This is the formula to calculate the number of occurances of the specific character in the given cell of Microsoft Office Excel.

=LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))

Here, A1 is the cell address you wish to calculate.
"a" is the character you wish to count.

LEN(A1) gives the total length, and
SUBSTITUTE(A1,"a","") replaces the occurances of "a" with empty so that those characters length will be reduced and the difference of the length gives the count of occurances of the character in the cell.

Excel cell - how to count number of occurances of a character  (985 Views)