Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
100 names consisting of a bunch of different letters, as you'd expect, how do I count the number of a's in the whole lot, number of b's number of c's? Without getting horribly manual about the whole thing?
Any help gratefully appreciated!
Thanks,
Ed
I'm not aware of an easy formula to do that. Its not something people would normally use excel for, so its unlikely to be trivial... ...assuming you don't want to build a macro (or even better create a programme outside excel to do it) then.
Assuming the name are in Column A (starting in row 1).
Insert a row above the names and but 1 in B1, and drag across so you get 2, 3, 4, etc [you will need to as far over as your longest name]
Inset this formula in B1: =IF(LEN($A2)>=B1,RIGHT(LEFT($A2,B1),1),"")
Drag it across the columns. You should see Joe Bloggs appear as J o e B l o g g s
Drag down your 100 name.
Now you can use something like
=COUNTIF(B2:AZ101, "A")
NOTE this is NOT case sensitive (i.e.. a=A)
You can obviously populate the whole alphabet by adapting that formula.
Try searching for letter frequency rather than letter counting. It's not an overly uncommon thing to look - letter coding in qualitative analysis and obviously basic cryptography.
For a list of names in column A from A1 to A100
and
A CAPITALISED alphabetical list in column B starting with A in B1 to Z in B26
Then in C1 paste, and drag to C26:
=SUMPRODUCT(LEN($A$1:$A$100)-LEN(SUBSTITUTE(UPPER($A$1:$A$100),B1,"")))
What this does is turns all the text in each cell of an array of cells (A1:A100) into upper case (UPPER), it then replaces all incidents of the Letter you are searching for (In the above formula that would be A referenced by B1) with a blank (""). It then compares the length of the original string (LEN) with the length of that string without the searched letter in and tots it up (SUMPRODUCT).
Slightly but not massively manual, do a search and replace for "a" replace with "a" and it will tell you how many times it was replaced.
Stoners method is rather elegant. I'd go with that.
Thanks all!
I had a feeling you'd pop up, Stoner. Thank you very much.
prego.
Worked a treat, cheers again. Her in charge can get ordering now.
And because I quite liked Greybeard's lateral thinking, I used that to check a couple.