I have a list of 501 names, and I need to find the occurrence of the letters from A-Z. I have 26 cells labeled A, B, C, etc, and want the occurrence of the letter from the list to go in the boxes. I've been trying for a while and this is the last formula I've attempted.
=LEN($A$2:$A$501)-LEN(SUBSTITUTE($A$2:$A$501,"A",""))
Any help is appreciated, thank you!
Hi, i'm not to sure exactly what you are asking.
Do you want excel to count the number of occurances of a certain letter in the column of names? (Number of names in the list that contains an "a") if so you can try the countif function.
https://exceljet.net/formula/count-cells-that-contain-specific-text
Or do you want excel to count the names starting with the letter?
Or do you want excel to extract the names from the list that contains each letter?
Your formula is right but with a slight modification (pay attention to Upper and Lowercase ).
For each row in a new column write this formula (assuming you have label in the first row)
Row2 LEN(A2)-LEN(SUBSTITUTE(A2,"A",""))
Row3 LEN(A3)-LEN(SUBSTITUTE(A3,"A","")) ....
...
Row507 LEN(A507)-LEN(SUBSTITUTE(A507,"A","")) ....
Copy in every other column changing the letter (eg. Row2 LEN(A2)-LEN(SUBSTITUTE(A2,"B","")) ....
You can use the label of the column and reference it in the formula (if B1 contains A):
Row2 LEN(A2)-LEN(SUBSTITUTE(A2,$B$1,""))
Row3 LEN(A3)-LEN(SUBSTITUTE(A3,$B$1,"")) ...
In this way you drag and copy the column from B to AA and you should be done.
PS You then sum all the 0s and 1s in row B508:AA508 to have the total count for each letter
There are probably many ways to count up letter frequencies in a list of names (or other words). Probably the most simple solution is: (1) copy & paste your table into MS Word, (2) search for each letter and record the number of occurrences for each. That's it! It's 26 searches, but it might be faster and less error-prone than writing formulas. I also like how Ivan and you are 'hacking' the substitution function, to remove text, and then count the loss of characters. Clever! Best wishes with your data, Kira. ~ Kevin
- Badges
- Science topic