Translate letters in a string to a specified numerical value and summing them up

[kamapaka]

Hi all,

could you please help me with the following issue?

1)

I have a table with numerical values specified for each letter, e.g., A is 1, B is 2, C is 3, J is 1 again, K is 2 and so on.

2)

I have an input in Excel - a string with letters, e.g. ABBA or MEDIEVAL

3)

I need to convert the letters to numbers and get their SUM, e.g. for ABBA it would be 1+2+2+1 = 6.

Could you please help me how to implement this in Excel?

The Excel file looks like this

Thank you very much in advance for you help!

[#2]

@kamapaka The attached file contains two possible solutions. Depending on your Excel version one of them may not work.

[#3]

@kamapaka 

One more variant

=SUMPRODUCT((MOD(CODE(MID(B17,ROW(INDIRECT("1:"&LEN(B17))),1))-65,9)+1)*1)

[#4]

@Riny_van_Eekelen 

thank you very much!

the solution with "sequence" did not work - my version of Excel does not support that function.

the other solution worked.

[#5]

@Sergei Baklan 

Thank you very much!

Is there any way to do the same with Cyrillic letters?

[#6]

@kamapaka 

It depends on which Cyrillic code page is used. For more or less standard CP866 formula could be

=SUMPRODUCT((MOD(CODE(MID(B23,ROW(INDIRECT("1:"&LEN(B23))),1))-192,9)+1)*1)

[#7]

@Sergei Baklan 

Dear Sergei,

Thank you very much!

It is almost what is needed with one exception: I need to account for the "Ё" letter and so all the relations letter:number after E change by +1. When I manually try to fix it, e.g. to change the number for Ж to 8 instead of 7, the program crashes.

I attached the file with complete and final correlations between Cyrillic characters and numbers.

Could you please take a look and help to make the correct formula?

Many thanks in advance for your great help!

P.S. I am sorry for not providing all the requirements at the beginning. To be honest, I could not imagine that there would be so many nuances.

[#8 · Accepted solution]

@kamapaka 

I opened this file on another computer and it show me another Cyrillic code page. Thus shifted in formula on UNICODE, perhaps that will be more reliable.

Formula is

=SUMPRODUCT(
   MOD(UNICODE(MID(B29,ROW(INDIRECT("1:"&LEN(B29))),1))-1040+
      (UNICODE(MID(B29,ROW(INDIRECT("1:"&LEN(B29))),1))>1045),9)+
   1+3*(UNICODE(MID(B29,ROW(INDIRECT("1:"&LEN(B29))),1))=1025)
)

[#9]

@Sergei Baklan 

Dear Sergei,

Thank you immensely! It works perfect! You are a genius!

[#10]

@kamapaka , you are welcome, glad to help