How do I count the number of times a letter is used in a cell?

J

jsrawlings

I want to be able to count the number of times a letter is used in a cell.
For example, if cell A2 contains the string "radar" I want to be able to
have excel return the number of times the letter "a" is used (2).
 
M

Marcelo

Hi,

use this code:

Press ALT+F11 copy and paste there
use =countchar("a";a1)

__________________________________________
Function CountChar(MyChar, Mystring)
Dim counter As Integer

CountChar = 0

For counter = 1 To Len(Mystring)
If Mid(Mystring, counter, 1) = MyChar Then CountChar = CountChar + 1
Next counter

End Function

_____________________________________

hope this helps
regards from Brazil
Marcelo





"jsrawlings" escreveu:
 
E

Elkar

Since SUBSTITUTE is case sensitive, you might also consider:

=LEN(D23)-LEN(SUBSTITUTE(SUBSTITUTE(D23,"A",""),"a",""))

This will count both "A" and "a"

HTH,
Elkar
 
D

Dave Peterson

Or just:

=LEN(D23)-LEN(SUBSTITUTE(Upper(D23),"A",""))


Since SUBSTITUTE is case sensitive, you might also consider:

=LEN(D23)-LEN(SUBSTITUTE(SUBSTITUTE(D23,"A",""),"a",""))

This will count both "A" and "a"

HTH,
Elkar
Click to expand...
 
Top