Calculate ASCII value of a string

MrByte

Board Regular
Joined
Feb 9, 2007
Messages
161
I want to get the sum of a string using the ASCII value. I tried this
Code:
=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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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.
 
Last edited:

MrByte

Board Regular
Joined
Feb 9, 2007
Messages
161
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
Feb 9, 2007
Messages
161
Got it thanks for the quick response!! Always a pleasure posting here. :cool:
 

Forum statistics

Threads
1,082,258
Messages
5,364,081
Members
400,778
Latest member
Canadian Sal

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top