characters and strings

How can I count how many times a certain letter is used in a string? For
example, I would like to calculate how many times the letter "a" occors in
the word "alphabetical" and any other series of words. Is there a macro that
can be used to help this problem?

=LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))

--
Kind regards,

Niek Otten

"elicamacho" <[email protected]> wrote in message news:[email protected]...
> How can I count how many times a certain letter is used in a string? For
> example, I would like to calculate how many times the letter "a" occors in
> the word "alphabetical" and any other series of words. Is there a macro that
> can be used to help this problem?

Assume cell A1:
=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="a"))


--
Regards,
Dave


"elicamacho" wrote:

> How can I count how many times a certain letter is used in a string? For
> example, I would like to calculate how many times the letter "a" occors in
> the word "alphabetical" and any other series of words. Is there a macro that
> can be used to help this problem?

Try this:

For any text in A1 and the text to find in B1

C1: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),""))
returns the count of B1 in the text in A1

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"elicamacho" wrote:

> How can I count how many times a certain letter is used in a string? For
> example, I would like to calculate how many times the letter "a" occors in
> the word "alphabetical" and any other series of words. Is there a macro that
> can be used to help this problem?

If you are searching for a multi-letter string:

A1: (the text to be searched)
B1: (the string to find in A1)
C1: =(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),"")))/LEN(B1)

For
A1: alphabetical
B1: AL
C1: returns 2

***********
Regards,
Ron

XL2002, WinXP-Pro


"elicamacho" wrote:

> How can I count how many times a certain letter is used in a string? For
> example, I would like to calculate how many times the letter "a" occors in
> the word "alphabetical" and any other series of words. Is there a macro that
> can be used to help this problem?