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

montyfern

Board Regular
Joined
Oct 12, 2017
Messages
65
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.


<colgroup><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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:
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Thank you. You as well. :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top