2

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.

7

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)

enter image description here

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.

3

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:

enter image description here


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

0
  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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.