assigned a value to characters and perform a mathematical functions ( ,-,*,/) of a string (having ch-CodePudding

I want to perform a sum of a given string ('ABCDEF') based on its pre-assigned values. i.e first I want to assign values to each character and then calculate the total value of the string having pre-assigned characters. Is it possible to carryout this function in excel? eg. is below

A=2, B=5, C=8, D=1, E=1, F=2
sum of 'ABCDE'=17
sum of 'FAC'=12

CodePudding user response：

So, this is what I have tried,

• Formula used in cell E1 --> Applicable to Excel 2021 & O365 Users Only

=SUMPRODUCT(VLOOKUP(MID(D1,SEQUENCE(LEN(D1)),1),$A$1:$B$6,2,0))

• Formula used in cell F1 --> Applicable to All Users

=SUMPRODUCT(VLOOKUP(MID(D1,ROW(INDIRECT("1:"&LEN(D1))),1),$A$1:$B$6,2,0))

Depending on Excel Version may or may need to press CTRL SHIFT ENTER for the above formula!

The above formula(s) works well when used for one Mathematical Operator, however if you want to use Multiple Operator, then I assume Defining Named Ranges for each of those characters and then use them for Mathematical functions, shall be an easy one!