How do i assign numbers to letters in excel?

I've been trying this for a while without success.

• I'm trying to assign the numbers 1-9 to letters of the alphabet A-Z [A=1 B=2 C=3 D=4 E=5 F=6 G=7 H=8 I=9 J=1 K=2 L=3 etc.]

• I want to be able to type a word and have the numbers corresponding to each letter show up, e.g. AND would be 154, FALL would be 6133.

• In another cell I want to be able to sum up the digits of the number, e.g. AND would be 1+5+4 = 10. FALL = 6+1+3+3 = 13.

asked Feb 27, 2021 at 10:43

Depending on your version of Excel, you can use:

Digits: =CONCAT[MOD[CODE[MID[H2,SEQUENCE[LEN[H2]],1]]-65,9]+1]
Sum:    =SUM[MOD[CODE[MID[H2,SEQUENCE[LEN[H2]],1]]-65,9]+1]

If your version does not have the SEQUENCE function, you can replace it with:

ROW[INDEX[$A:$A,1]:INDEX[$A:$A,LEN[H2]]]

If you do not have the CONCAT function, VBA might be a better choice.

answered Feb 27, 2021 at 11:57

Ron RosenfeldRon Rosenfeld

7,4163 gold badges13 silver badges16 bronze badges

You can use VBA functions for that:

Public Function AlphaToDigits[s]
    Dim d As String
    
    For i = 1 To Len[s]
        d = d & CStr[[[Asc[Mid[s, i, 1]] - 65] Mod 9] + 1]
    Next
    
    AlphaToDigits = d
    
End Function

Public Function SumStringDigits[s]
    Dim n As Long
    
    For i = 1 To Len[s]
        n = n + CInt[Mid[s, i, 1]]
    Next
    
    SumStringDigits = n
    
End Function

For example, with =AlphaToDigits[A1] etc. in column B and =SumStringDigits[B1] etc. in column C:

In case you haven't discovered macros in Excel, here's Microsoft's instructions on how to create them: Create custom functions in Excel.

answered Feb 27, 2021 at 11:14

Andrew MortonAndrew Morton

2,7772 gold badges18 silver badges29 bronze badges

1. Your Word say "ORANGE" is there in Cell A1

2. Split the word in single letter, on the right side by entering formula :

3. B1=MID[$A$1,1,1]; C1=MID[$A$1,2,1]; D1=MID[$A$1,3,1].....So on....

4. Leaving One Row, for clarity : In Cell B3 Enter Formula Given in Step 5.

5. B3 =IF[CODE[B1]