SOLVED

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

Highlighted
Occasional Contributor

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

Hi all,

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.

The Excel file looks like this

Thank you very much in advance for you help!

9 Replies
Highlighted

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

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

Highlighted

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

One more variant

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

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

thank you very much!

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

the other solution worked.

Highlighted

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

Thank you very much!

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

Highlighted

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

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)``

Highlighted

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

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?

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.

Highlighted
Best Response confirmed by kamapaka (Occasional Contributor)
Solution

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

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)
)``````

Highlighted

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

Dear Sergei,

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

Highlighted

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

@kamapaka , you are welcome, glad to help