Just like that, each alphabet corresponds to a number, summing up alphabets
I cant really see how this post could be useful to you. One of my friends who believed in numerology brought this problem to me of converting names to numbers. Here is the solution that we worked out. You may treat this as a Just Like That entry until it finds any use.
Lets say the names are stored in column A, have worked out a numerical equivalent in column B using the equivalence (A or a =1) and (Z or z = 26) with all other alphabets in between
Method 1: Using CODE function
For windows CODE(text) returns the ANSI code of the first character in the text string.
- code(“A”)=65 and code(“Z”)=90 with all other upper case alphabets in between
- code(“a”)=97 and code(“z”)=122 with all other lower case alphabets in between
The formula in B2 {=SUM(CODE(MID(LOWER(A2),ROW(INDIRECT(“1:”&LEN(A2))),1))-96)}
entered as an array formula with ctrl+shift+enter
Here lower function is used so that all alphabets are treated as lower case and ansi code is from 97 to 122
Hence, 96 is subtracted so that a has numerical equivalent of 1
Cell A4 (Anup Agarwal) has space between the first name and last name. We can use substitute function to replace the space with no space. The revised formula in cell B4 is
B4 {=SUM(CODE(MID(LOWER(SUBSTITUTE(A4,” “,””)),ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A4,” “,””)))),1))-96)}
again entered as an array formula with ctrl+shift+enter
Method 2: Using lookup function
You can avoid using CODE function and use the lookup function to achieve the same result. Have defined the following table as equivalent
The formula entered in B2 {=SUM(LOOKUP(MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1),equivalent))}
entered as an array formula with ctrl+shift+enter
The formula entered in B4 is
{=SUM(LOOKUP(MID(SUBSTITUTE(A4,” “,””),ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A4,” “,””)))),1),equivalent))}
entered as an array formula with ctrl+shift+enter
Have fun and let me know if you ever find use of it 🙂
Useful Excel Tips 