how to count the number of occurences of a letter ?

R

ruddy.annette

If I have a text "once upon a time" in a cell A1.
How can I know the number of 'o' or 'n' contained in that text ?
Thanks
 
P

Pete_UK

You can put this in B1:

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

to count the number of 'o' - just change the "o" to "n" for other
letters - or put n in C1 and change B1 to:

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

then you can easily put other letters in C1 to get a count of each.

Hope this helps.

Pete
 
M

Mike H

And a non case sensitive version

=(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),LOWER("o"),"")))/LEN("o")

Mike
 
R

ruddy.annette

And a non case sensitive version

=(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),LOWER("o"),"")))/LEN("o")

Mike





- Show quoted text -
Click to expand...

thanks very much Pete and Mike. The idea is excellent.
 
Top