Count the Specific Char

[sethu29]

HI Support,


Can any one tell me a formula to find out a specific character in a range.

For Example:

There are 10 words placed in a range from A1 to A10. I want to know How many Character "a" are there in this range. How to find out the count in this case by formula. I want to count this through formula.

Ramu
Sethu
beauty
raju
puma
neha
dinesh
sathish
ashwin
raja

[Teeroy]

It would be easy to write a UDF in VBA that you could use in a formula to do this but I can't think of a simple way from built in formula.
One 2 step method is to split text to columns of fixed width 1 (say 20 columns to account for longer words) then use =COUNTIF(A1:T10,"a").

[sethu29]

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))) This formula is helpfull. But i cant able to specify the range.

[Teeroy]

Try
=SUM(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"a",""))) in B1 but enter as an array formula.

[Mikey]

You could also use SUMPRODUCT to avoid "array entry" like this


=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"a","")))

Because SUBSTITUTE is case-sensistive that only counts "a"s not "A"s. If you want both change to


=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),"a","")))

[sethu29]

Great Mikey........ Thanks......