Convert words into a numeric value

Hello,

I am trying to convert words into a numeric value by assigning numbers to each letter. If I use ;

A = 1
B = 2
C = 3 etc

Down to, Z=26

I have the formula , =SUMPRODUCT(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))-96) which works perfectly.

I have been trying to create a new formula that will allow me to assign different values to the alphabet (not 1 to 26) but have not been successful.

Most formulas I try only give me the first letter in a word. I would like to type a word in a cell and have a formula that will assign specific values to each letter and add them up. In the adjacent cell.

Any help will be greatly appreciated.

Thank you.

Thank you for the formula. I will try it and see how I go. I have attached a spreadsheet which might better outline what I am trying to achieve.
It is a type of numerology. "Table A" is the values I would like to assign to letters A to Z. "Table 1" is using simple numerology with the values of the alphabet being 1 to 26 with the third column reducing the calculated value down to a number between 1 and 9. Notice how in this table the value of all the letters of the word in the cell are added together.
In "Table 2" I am trying to achieve the same thing as "Table 1" but using the values for A to Z as listed in "Table A". So far I have been unsuccessful as the formula only calculates the value of the first letter in the word rather than calculating all the letters in the word and adding them together.
Thank you everyone for your help.

numerology .xlsx

i have found a solution on another thread but would love to see if someone could correct my equation in Table 2 to add the correct values of all the letters in the word rather than just give the value of the first letter. cheers to anyone who can help.