assigned a value to characters and perform a mathematical functions (+,-,*,/) of a string (having characters) in MS excel

Question

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

Answer 1

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!