# Excel: assign numbers 1-9 to letters

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.

## 3 Answers

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.

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.

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)<=73,CODE(B1)-64, IF(CODE(B1)<=82,CODE(B1)-73, IF(CODE(B1)<=90,CODE(B1)-82,0)))

6. COPY Formula of Cell B3, in C3, D3, E3, F3,......etc.

7. For SUM, enter formula in say, M3 = SUM(B3:K3)

8. On entering formula in B3..to G3, we get, values as : 691575 and Sum=33.

Thanks.