Counting Individual characters in ONE cell, which each cell changes the character to count

[montyfern]

Hi Everyone,

I've scoured previous posts in the hopes of not creating a new thread, and found some great stuff, but it's not 100% accurate and also I'm not getting it. Here's what I'm trying to do, please:

Cell A1 has the word AARDVARK. Count how many A's are in that word. We all know it's three. This formula works well, but I have to specify each "new" letter to count: =LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),UPPER("A"),""))
So, in cell A2 we have ANTEATER. Same formula counts "2". But in Cell A3 is the word POPULAR. Count the "P"'s, so I have to edit the formula to "P". Cell A4 wants to count the O's in ZOOLOGY. Make sense? So I can't auto-fill as each variable of the text character they want to count CHANGES.

Any ideas are greatly appreciated except macros.

[TABLE="width: 244"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

The only way I can think of is to place the letter you want to count in column B to the right of the word and place this formula in column=LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),UPPER(B2),"")) C:

 

[montyfern]

Hi Mumps, thanks! I tested it and it works superbly. I guess there isn't a way in Excel to specify a particular character or text string from ONE cell short of using macros. Thanks so much for your fast reply.

 

[mumps]

You are very welcome. You are correct. You would need a macro but even then, you would have enter the letter to search individually for each word.

 

[Fluff]

Depending on what you are trying to do, you could use
=LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),UPPER(B$1),""))
and fill down, this will give the count of whatever letter is in B1 for each cell in col A

 

[montyfern]

You are very welcome. You are correct. You would need a macro but even then, you would have enter the letter to search individually for each word.

Again, thanks Mumps! Have a terrific day...

 

[mumps]

Thank you. You as well.