Find Number of a Letter
Here was the request:
I have a spreadsheet (Excel XP) which has a column of text - the text needs to be scanned and the first letter (Left) of the text needs to be converted to it's relevant number relating to the alphabet (ie a=1 z=26) and this result placed in another cell. This needs to be done so the whole column then has a second column showing it's corresponding number.
I thought about it a few days and this elegant formula hit me.
=CODE(LEFT(UPPER(A4),1))-64
The CODE function returns the ASCII value of a character. "A" being 65 and "Z" 90. Case also matters so I have to convert to upper case. Then I subtract 64 and this gives you the ordinal position of the letter in the alphabet. ~~~~ Thanks to Ken P.
=FIND(LEFT(A25,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ",1)
Using the FIND command, get the left character LEFT(A25,1), then find its position in the array
“ABCDEFGHIJKLMNOPQRSTUVWXYZ” starting at position 1.
Then all you need to do is fill down. Even if you sort, the find continues to work.
|