Count the number of appearance within a cell

[Lucy]

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.

 

[T. Valko]

Try this:

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

Note that SUBSTITUTE is case sensitive. So, enter the character you want to
count in upper case. Or, you can enter the character in a cell then refer to
that cell:

B1 = P or p

Then:

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

 

[Teethless mama]

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"p","P"},"")))

 

[T. Valko]

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",""))

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

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

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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.

 

[Rick Rothstein \(MVP - VB\)]

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\)]

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.

Glad to be of help. <g>

Rick

 

[T. Valko]

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!

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Lucy]

yes, i use this one. Thanks!!

Try this:

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

Note that SUBSTITUTE is case sensitive. So, enter the character you want to
count in upper case. Or, you can enter the character in a cell then refer to
that cell:

B1 = P or p

Then:

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

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

yes, i use this one. Thanks!!