Forum Discussion
Translate letters in a string to a specified numerical value and summing them up
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!
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 Replies
One more variant
=SUMPRODUCT((MOD(CODE(MID(B17,ROW(INDIRECT("1:"&LEN(B17))),1))-65,9)+1)*1)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)
kamapaka The attached file contains two possible solutions. Depending on your Excel version one of them may not work.
thank you very much!
the solution with "sequence" did not work - my version of Excel does not support that function.
the other solution worked.
