SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1461375%22%20slang%3D%22en-US%22%3ETranslate%20letters%20in%20a%20string%20to%20a%20specified%20numerical%20value%20and%20summing%20them%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1461375%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3Ecould%20you%20please%20help%20me%20with%20the%20following%20issue%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%3C%2FP%3E%3CP%3EI%20have%20a%20table%20with%20numerical%20values%20specified%20for%20each%20letter%2C%20e.g.%2C%20A%20is%201%2C%20B%20is%202%2C%20C%20is%203%2C%20J%20is%201%20again%2C%20K%20is%202%20and%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%3C%2FP%3E%3CP%3EI%20have%20an%20input%20in%20Excel%20-%20a%20string%20with%20letters%2C%20e.g.%20ABBA%20or%20MEDIEVAL%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3)%3C%2FP%3E%3CP%3EI%20need%20to%20convert%20the%20letters%20to%20numbers%20and%20get%20their%20SUM%2C%20e.g.%20for%20ABBA%20it%20would%20be%201%2B2%2B2%2B1%20%3D%206.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20help%20me%20how%20to%20implement%20this%20in%20Excel%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Excel%20file%20looks%20like%20this%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22excel_numbers.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F198608i72C83808822E7E8F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22excel_numbers.jpg%22%20alt%3D%22excel_numbers.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20in%20advance%20for%20you%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1461375%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1461405%22%20slang%3D%22en-US%22%3ERe%3A%20Translate%20letters%20in%20a%20string%20to%20a%20specified%20numerical%20value%20and%20summing%20them%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1461405%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F698774%22%20target%3D%22_blank%22%3E%40kamapaka%3C%2FA%3E%26nbsp%3BThe%20attached%20file%20contains%20two%20possible%20solutions.%20Depending%20on%20your%20Excel%20version%20one%20of%20them%20may%20not%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1461552%22%20slang%3D%22en-US%22%3ERe%3A%20Translate%20letters%20in%20a%20string%20to%20a%20specified%20numerical%20value%20and%20summing%20them%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1461552%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F698774%22%20target%3D%22_blank%22%3E%40kamapaka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20more%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT((MOD(CODE(MID(B17%2CROW(INDIRECT(%221%3A%22%26amp%3BLEN(B17)))%2C1))-65%2C9)%2B1)*1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1461881%22%20slang%3D%22en-US%22%3ERe%3A%20Translate%20letters%20in%20a%20string%20to%20a%20specified%20numerical%20value%20and%20summing%20them%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1461881%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20very%20much!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20solution%20with%26nbsp%3B%22sequence%22%20did%20not%20work%20-%20my%20version%20of%20Excel%20does%20not%20support%20that%20function.%3C%2FP%3E%3CP%3Ethe%20other%20solution%20worked.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1461886%22%20slang%3D%22en-US%22%3ERe%3A%20Translate%20letters%20in%20a%20string%20to%20a%20specified%20numerical%20value%20and%20summing%20them%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1461886%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much!%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20to%20do%20the%20same%20with%20Cyrillic%20letters%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1461915%22%20slang%3D%22en-US%22%3ERe%3A%20Translate%20letters%20in%20a%20string%20to%20a%20specified%20numerical%20value%20and%20summing%20them%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1461915%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F698774%22%20target%3D%22_blank%22%3E%40kamapaka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20depends%20on%20which%20Cyrillic%20code%20page%20is%20used.%20For%20more%20or%20less%20standard%20CP866%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT((MOD(CODE(MID(B23%2CROW(INDIRECT(%221%3A%22%26amp%3BLEN(B23)))%2C1))-192%2C9)%2B1)*1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20261px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F198632i267E31841B3D1547%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1463863%22%20slang%3D%22en-US%22%3ERe%3A%20Translate%20letters%20in%20a%20string%20to%20a%20specified%20numerical%20value%20and%20summing%20them%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1463863%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDear%20Sergei%2C%3C%2FP%3E%3CP%3EThank%20you%20very%20much!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20almost%20what%20is%20needed%20with%20one%20exception%3A%20I%20need%20to%20account%20for%20the%20%22%D0%81%22%20letter%20and%20so%20all%20the%20relations%20letter%3Anumber%20after%20E%20change%20by%20%2B1.%20When%20I%20manually%20try%20to%20fix%20it%2C%20e.g.%20to%20change%20the%20number%20for%20%D0%96%20to%208%20instead%20of%207%2C%20the%20program%20crashes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20the%20file%20with%20complete%20and%20final%20correlations%20between%20Cyrillic%20characters%20and%20numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20take%20a%20look%20and%20help%20to%20make%20the%20correct%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance%20for%20your%20great%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EP.S.%20I%20am%20sorry%20for%20not%20providing%20all%20the%20requirements%20at%20the%20beginning.%20To%20be%20honest%2C%20I%20could%20not%20imagine%20that%20there%20would%20be%20so%20many%20nuances.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1467664%22%20slang%3D%22en-US%22%3ERe%3A%20Translate%20letters%20in%20a%20string%20to%20a%20specified%20numerical%20value%20and%20summing%20them%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1467664%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F698774%22%20target%3D%22_blank%22%3E%40kamapaka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20opened%20this%20file%20on%20another%20computer%20and%20it%20show%20me%20another%20Cyrillic%20code%20page.%20Thus%20shifted%20in%20formula%20on%20UNICODE%2C%20perhaps%20that%20will%20be%20more%20reliable.%3C%2FP%3E%0A%3CP%3EFormula%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(%0A%20%20%20MOD(UNICODE(MID(B29%2CROW(INDIRECT(%221%3A%22%26amp%3BLEN(B29)))%2C1))-1040%2B%0A%20%20%20%20%20%20(UNICODE(MID(B29%2CROW(INDIRECT(%221%3A%22%26amp%3BLEN(B29)))%2C1))%26gt%3B1045)%2C9)%2B%0A%20%20%201%2B3*(UNICODE(MID(B29%2CROW(INDIRECT(%221%3A%22%26amp%3BLEN(B29)))%2C1))%3D1025)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20353px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F199011i5F8AB352AD120F98%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1468935%22%20slang%3D%22en-US%22%3ERe%3A%20Translate%20letters%20in%20a%20string%20to%20a%20specified%20numerical%20value%20and%20summing%20them%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1468935%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDear%20Sergei%2C%3C%2FP%3E%3CP%3EThank%20you%20immensely!%20It%20works%20perfect!%20You%20are%20a%20genius!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1468976%22%20slang%3D%22en-US%22%3ERe%3A%20Translate%20letters%20in%20a%20string%20to%20a%20specified%20numerical%20value%20and%20summing%20them%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1468976%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F698774%22%20target%3D%22_blank%22%3E%40kamapaka%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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

excel_numbers.jpg

Thank you very much in advance for you help!

9 Replies
Highlighted

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

Highlighted

@kamapaka 

One more variant

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

@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.

Highlighted

@Sergei Baklan 

Thank you very much!

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

Highlighted

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

 

image.png

Highlighted

@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.

Highlighted
Best Response confirmed by kamapaka (Occasional Contributor)
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)
)

image.png

Highlighted

@Sergei Baklan 

Dear Sergei,

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

 

 

Highlighted