Forum Discussion
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) )
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)
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.
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) )kamapaka , you are welcome, glad to help