Calculate ASCII value of a string

[MrByte]

I want to get the sum of a string using the ASCII value. I tried this

=SUM(MID(A1,1,LEN(A1)))

but of course I get the #VALUE! error. So for example the word Black will get a sum of 477. The ASCII values of each letter are 66 108 97 99 107 and when summed I get the 477.

 

[Jonmo1]

Try

=SUMPRODUCT(CODE(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),1)))

Do not adjust the part I highlighted blue, it has no relation to your data.

 

[MrByte]

Thanks! it worked for all but one that I have listed right now. The word being Camo gives me #VALUE! error. Any ideas? Oh and the position in the column is the last one at row 7.

 

[Jonmo1]

Recopy the formula from my post, I edited it slightly to add absolute references..
It would have gotten messed up when filled down without the absolute refs..

 

[MrByte]

Got it thanks for the quick response!! Always a pleasure posting here.

 

[Jonmo1]

Glad to help, thanks for the feedback.