Count the number of appearance within a cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lucy - 08 Mar 2008 05:20 GMT

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.

Reply to this Message

T. Valko - 08 Mar 2008 05:34 GMT

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

Signature

Biff
Microsoft Excel MVP

> In cell A1, I put text "Apple"
>
[quoted text clipped - 3 lines]
>
> Thanks.

Reply to this Message

Lucy - 09 Mar 2008 19:47 GMT

yes, i use this one. Thanks!!

> Try this:
>
[quoted text clipped - 17 lines]
> >
> > Thanks.

Reply to this Message

T. Valko - 09 Mar 2008 21:56 GMT

You're welcome!

Signature

Biff
Microsoft Excel MVP

> yes, i use this one. Thanks!!
>
[quoted text clipped - 21 lines]
>> >
>> > Thanks.

Reply to this Message

Teethless mama - 08 Mar 2008 14:02 GMT

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

> In cell A1, I put text "Apple"
>
[quoted text clipped - 3 lines]
>
> Thanks.

Reply to this Message

T. Valko - 08 Mar 2008 20:30 GMT

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

Signature

Biff
Microsoft Excel MVP

> =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"p","P"},"")))
>
[quoted text clipped - 5 lines]
>>
>> Thanks.

Reply to this Message

Rick Rothstein (MVP - VB) - 08 Mar 2008 20:56 GMT

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

Reply to this Message

T. Valko - 08 Mar 2008 21:27 GMT

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

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

Signature

Biff
Microsoft Excel MVP

> See inline comments...
>
[quoted text clipped - 13 lines]
>
> Rick

Reply to this Message

Rick Rothstein (MVP - VB) - 08 Mar 2008 21:51 GMT

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.

> Yeah, but look how much shorter the UPPER version is!
>
[quoted text clipped - 17 lines]
>>
>> Rick

Reply to this Message

T. Valko - 08 Mar 2008 22:10 GMT

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.

Signature

Biff
Microsoft Excel MVP

> 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"
[quoted text clipped - 31 lines]
>>>
>>> Rick

Reply to this Message

Rick Rothstein (MVP - VB) - 08 Mar 2008 22:14 GMT

> 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

Reply to this Message

Rick Rothstein (MVP - VB) - 08 Mar 2008 22:12 GMT

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

> 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"
[quoted text clipped - 31 lines]
>>>
>>> Rick

Reply to this Message

T. Valko - 08 Mar 2008 22:21 GMT

>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!

Signature

Biff
Microsoft Excel MVP

> 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
[quoted text clipped - 42 lines]
>>>>
>>>> Rick

Reply to this Message

Rick Rothstein (MVP - VB) - 08 Mar 2008 22:30 GMT

> >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

Reply to this Message