Count of Occurrences

Posted by Fred on September 09, 2001 3:51 PM

How can I get a count of the number of occurrences of a specific character within a cell ?

For example if a cell contains, XZCBCABC, I would like to know how many C's are in the cell. I've searched the function list without success. Thanks for your help.

Posted by Martin on September 09, 2001 5:26 PM

Case sensitive :-
=LEN(A1)-LEN(SUBSTITUTE(A1,"C",""))
or
=LEN(A1)-LEN(SUBSTITUTE(A1,"c",""))

Not case sensitive :-
=LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"C",""),"c",""))

Posted by Fred on September 09, 2001 6:23 PM

Deleting the characters to be counted and then comparing the original length to the new length.
CLEVER !! and thanks Martin !!