Counting the frequency of specific letters/characters contianed in a single cell

[jjm204]

I am looking for a formula or function to count the number of times an individual letter or character appears in 1 single cell. Example:

Cell A2 contains the text: "A fast fox jumped over the fence!"

Column B: Column C:
"A"
"B"
"C"
"D"
"E"
...ect.
"!"

In Column C I'm hoping to create a function/formula that will tell me how many times the letter "A" or "a" appears. So in Column C, next to the letter "A" in Column B, I'm wanting it to automatically calculate the answer should be 2. For the two letter a's in "A fast...". Repeat for each letter of the alphabet including punctuation characters.

Letter "E" would display the answer is 5.
Character "!" would display the answer is 1.
... and so on.

Any insight would be helpful!!

Jeff

 

[XOR LX]

Hi,

Assuming your search strings begin in B2, and are in upper case where appropriate, in C2 and copy down:

=LEN(A$2)-LEN(SUBSTITUTE(UPPER(A$2),B2,""))

Regards

 

[jjm204]

That works perfectly!! What tweak to the formula would be necessary to add the functionality of getting a separate count for capital letters and lowercase letters?... assuming of course I had an upper and lower case string starting in B2?

 

[Aladin Akyurek]

That works perfectly!! What tweak to the formula would be necessary to add the functionality of getting a separate count for capital letters and lowercase letters?... assuming of course I had an upper and lower case string starting in B2?

=LEN($A$2)-LEN(SUBSTITUTE($A$2,$E2,""))

where E2 might be a letter of any type for SUBSTITUTE is case sensitive.