Jun 14 2020 01:27 AM
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!
Jun 14 2020 02:19 AM
@kamapaka The attached file contains two possible solutions. Depending on your Excel version one of them may not work.
Jun 14 2020 04:59 AM
One more variant
=SUMPRODUCT((MOD(CODE(MID(B17,ROW(INDIRECT("1:"&LEN(B17))),1))-65,9)+1)*1)
Jun 14 2020 09:40 AM
thank you very much!
the solution with "sequence" did not work - my version of Excel does not support that function.
the other solution worked.
Jun 14 2020 09:43 AM
Jun 14 2020 10:12 AM
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)
Jun 15 2020 09:57 AM
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.
Jun 16 2020 07:00 AM
SolutionI 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)
)
Jun 16 2020 12:33 PM
Jun 16 2020 12:52 PM
@kamapaka , you are welcome, glad to help
Jun 16 2020 07:00 AM
SolutionI 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)
)