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

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

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:

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

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


"Bearacade" wrote:

>
> =LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))
>
>
> --
> Bearacade
>
>
> ------------------------------------------------------------------------
> Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> View this thread: http://www.excelforum.com/showthread...hreadid=556259
>
>

Or just:

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



Elkar wrote:
>
> 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
>
> "Bearacade" wrote:
>
> >
> > =LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))
> >
> >
> > --
> > Bearacade
> >
> >
> > ------------------------------------------------------------------------
> > Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> > View this thread: http://www.excelforum.com/showthread...hreadid=556259
> >
> >

--

Dave Peterson

So will this:

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"A",""))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Elkar" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Bearacade" wrote:
>
>>
>> =LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))
>>
>>
>> --
>> Bearacade
>>
>>
>> ------------------------------------------------------------------------
>> Bearacade's Profile:
>> http://www.excelforum.com/member.php...o&userid=35016
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=556259
>>
>>