characters and strings

E

elicamacho

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?
 
D

David Billigmeier

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

Ron Coderre

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
 
R

Ron Coderre

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
 
Top