| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
characters and strings
=LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))
-- Kind regards, Niek Otten "elicamacho" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
characters and strings
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
characters and strings
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
characters and strings
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| hash function for large strings | Excel Worksheet Functions | |||
| VB Assigning Values to a Series of Strings | Excel Discussion (Misc queries) | |||
| Wildcard MATCH() breaks on long (?) strings | Excel Worksheet Functions | |||
| Writing Localized Strings In Cells Using Automation | Excel Discussion (Misc queries) | |||
| How can I find strings of wildcards in Excel? | Excel Discussion (Misc queries) | |||
Linear Mode
