How to calculate Wordvalue (a=1,b=2) icecream=57

[martinafl]

I want to turn letters into numbers (A=1, B=2, C=3, ..., Z=26) and calculate the sum. For example in cell A1 is the word icecream , cell B1 should then display 57
(i=9 + c=3 + e=5 +c=3 + r=18 + e=5 + a=1 + m=13 = 57)

There are online calculators, but I would like to calculate that with Excel (Office 2013), I also found several explanations for German excel, but none for English Excel.

Thanks!
Martina

[Andy Pope]

One way using array formula, so use CTRL+SHIFT+ENTER to commit formula

=SUM(CODE(MID(UPPER(SUBSTITUTE(A1," ","")),ROW(A1:INDEX(A:A,LEN(SUBSTITUTE(A1," ","")))),1))-64)

[martinafl]

Thanks Andy, it worked for A1 with Icecream. I input your formula and pressed use CTRL+SHIFT+ENTER, it worked.

However, in A2 there is "New York", result should be 111, instead it displays a wrong value. I changed all the A1 in your formule into A2, but still it did not work there.

Sorry for this seamingly little question, but which A1 to change or what else to keep in mind?

[Andy Pope]

For A2 the formula would be, I have also made the relevant cells absolute

=SUM(CODE(MID(UPPER(SUBSTITUTE(A2," ","")),ROW($A$1:INDEX($A:$A,LEN(SUBSTITUTE(A2," ","")))),1))-64)

[martinafl]

Thank you, it worked perfectly :-)

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.