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

Anish, this formula does exactly what I need.

Thank you so very much for taking time to respond.

Answer 3

Thank you for providing a response.

The formula needs to be carried through to a separate worksheet, so that when a letter is entered, the cell below will assign the the relevant number so it looks like the example attached:

The sequence is listed on a separate tab as shown:

Thank you, Rachael