Multiple problem formula required.

Question

Hi. I am trying to create a formula so that I can enter any letter from the alphabet into a cell, and it will return with a number from 1-9
For example; A=1, B=2.... I=9, J=1, K=2,.... etc, so Z=8. The letters go from 1-9 and then start at 1 again, so the formula will read the letter and return the corresponding number.
I'm basically converting names into numbers, so I need to calculate the total number (I hope this makes sense). For example, L I S A would return 3 + 9 + 1 + 1
If you're able to assist, thank you

Answer 1

Hi,

In cell E1, enter this formula

=MOD(SUM(XLOOKUP(MID(D1,SEQUENCE(LEN(D1)),1),A1:A26,B1:B26))-1,9)+1

Hope this helps.

Answer 2

You are welcome.

Answer 3

Thank you. I wasn't sure of which formula that was needed. Thank you for clarifying, and taking the time to respond.