Counting the number of times a specific character appears in a cell

P

PCLIVE

Is there a function that I can use to determine the number of times the
letter "Y" appears in a single cell?

Thanks,
Paul
 
D

Domenic

Try...

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

....which is case-sensitive. If you don't want it to be case-sensitive,
try the following instead...

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"Y",""))

or

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),""))

....where B1 contains the letter of interest.

Hope this helps!
 
P

PCLIVE

Works great!

Thanks,
Paul

Domenic said:
Try...

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

...which is case-sensitive. If you don't want it to be case-sensitive,
try the following instead...

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"Y",""))

or

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),""))

...where B1 contains the letter of interest.

Hope this helps!
Click to expand...
 
A

Ashish Mathur

Hi,

You may try the following array formula (Ctrl+Shift+Enter)

=COUNT(IF(MID($C$4,ROW(1:20),1)="y",1))

$C$4 isthe cell containing the word in which you want to count the y's

Regards,

Ashish Mathur
 
Top