A1 = St. Louis

=LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},""))

Fails

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},"")))
=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"S",""))


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"p","P"},"")))


"Lucy" wrote:

In cell A1, I put text "Apple"

How can I count the number of appearance of letter "p" within one cell?

I would like the answer come out as numeric "2"

Thanks.

See inline comments...

A1 = St. Louis

=LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},""))

Fails

I'm not sure why you posted the above comment... no one posted that formula.

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},"")))

The above formula which you posted is the formula that Teethless mama posted
in the message you responded to.


Rick

Yeah, but look how much shorter the UPPER version is!

I thought for sure you'd be "all over it". <BG

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
See inline comments...

A1 = St. Louis

=LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},""))

Fails

I'm not sure why you posted the above comment... no one posted that
formula.

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},"")))

The above formula which you posted is the formula that Teethless mama
posted in the message you responded to.


Rick

If we assume the text is not longer than 999 characters (the limit can be
made larger if needed), this array-entered** formula really is "much"
shorter...

=SUM(--(MID(A4,ROW($1:$999),1)="s"))

<vbg

Rick

** For those reading this thread who are unfamiliar with array-entered
formulas, they are committed by pressing Ctrl+Shift+Enter instead of just
Enter.


"T. Valko" wrote in message
...
Yeah, but look how much shorter the UPPER version is!

I thought for sure you'd be "all over it". <BG

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
See inline comments...

A1 = St. Louis

=LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},""))

Fails

I'm not sure why you posted the above comment... no one posted that
formula.

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},"")))

The above formula which you posted is the formula that Teethless mama
posted in the message you responded to.


Rick

That's the Rick I know and love! <g

Oh, thanks for posting that. I've been meaning to do some tests on something
and that formula reminded of it.


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
If we assume the text is not longer than 999 characters (the limit can be
made larger if needed), this array-entered** formula really is "much"
shorter...

=SUM(--(MID(A4,ROW($1:$999),1)="s"))

<vbg

Rick

** For those reading this thread who are unfamiliar with array-entered
formulas, they are committed by pressing Ctrl+Shift+Enter instead of just
Enter.


"T. Valko" wrote in message
...
Yeah, but look how much shorter the UPPER version is!

I thought for sure you'd be "all over it". <BG

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in message ...
See inline comments...

A1 = St. Louis

=LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},""))

Fails

I'm not sure why you posted the above comment... no one posted that
formula.

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},"")))

The above formula which you posted is the formula that Teethless mama
posted in the message you responded to.


Rick

That's the Rick I know and love! <g

I wouldn't want to disappoint, you know... <bg

Oh, thanks for posting that. I've been meaning to do some tests on
something and that formula reminded of it.

Glad to be of help. <g

Rick

Just out of curiosity, how efficient do you think this formula would be? I'm
not sure what drag the 999-iteration array calculations are putting on the
system (I wonder if there is a short cut mechanism built into the MID
function evaluator to not attempt to calculate past the end of the text),
although I'm guessing the SUBSTITUTE function involves an internal loop of
some kind to help offset it. And, of course, if we knew the text would
always be less than 100 characters in length, we could reduce the drag from
the array calculations by a factor of 10.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
If we assume the text is not longer than 999 characters (the limit can be
made larger if needed), this array-entered** formula really is "much"
shorter...

=SUM(--(MID(A4,ROW($1:$999),1)="s"))

<vbg

Rick

** For those reading this thread who are unfamiliar with array-entered
formulas, they are committed by pressing Ctrl+Shift+Enter instead of just
Enter.


"T. Valko" wrote in message
...
Yeah, but look how much shorter the UPPER version is!

I thought for sure you'd be "all over it". <BG

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in message ...
See inline comments...

A1 = St. Louis

=LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},""))

Fails

I'm not sure why you posted the above comment... no one posted that
formula.

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},"")))

The above formula which you posted is the formula that Teethless mama
posted in the message you responded to.


Rick

how efficient do you think this formula would be?

Not very.

You could limit the iterations by calculating the actual length of the
string but then there's go your nice short formula!


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
Just out of curiosity, how efficient do you think this formula would be?
I'm not sure what drag the 999-iteration array calculations are putting on
the system (I wonder if there is a short cut mechanism built into the MID
function evaluator to not attempt to calculate past the end of the text),
although I'm guessing the SUBSTITUTE function involves an internal loop of
some kind to help offset it. And, of course, if we knew the text would
always be less than 100 characters in length, we could reduce the drag
from the array calculations by a factor of 10.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
If we assume the text is not longer than 999 characters (the limit can be
made larger if needed), this array-entered** formula really is "much"
shorter...

=SUM(--(MID(A4,ROW($1:$999),1)="s"))

<vbg

Rick

** For those reading this thread who are unfamiliar with array-entered
formulas, they are committed by pressing Ctrl+Shift+Enter instead of just
Enter.


"T. Valko" wrote in message
...
Yeah, but look how much shorter the UPPER version is!

I thought for sure you'd be "all over it". <BG

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in message ...
See inline comments...

A1 = St. Louis

=LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},""))

Fails

I'm not sure why you posted the above comment... no one posted that
formula.

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},"")))

The above formula which you posted is the formula that Teethless mama
posted in the message you responded to.


Rick

how efficient do you think this formula would be?

Not very.

So I am guessing, then, that the MID function evaluator isn't smart enough
to automatically stop calculating when it reaches the end of the text.
Although I presume the forced array looping mechanism isn't a speed demon
either.

You could limit the iterations by calculating the actual length of
the string but then there's go your nice short formula!

No way! I made a major concession length-wise when I included the absolute
reference symbols in the ROW function call just so the formula would be able
to be copied down.<g

Rick