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

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


On Nov 26, 12:55*pm, wrote:
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

And a non case sensitive version

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

Mike

" wrote:

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

On Nov 26, 1:28*pm, Mike H wrote:
And a non case sensitive version

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

Mike



" wrote:
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- Hide quoted text -

- Show quoted text -

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