Convert Text to Numeric value

Hi there,

Wondering if it is possible to convert a string into a numeric value. The idea is that if you have a list of names, if you could add up the numeric values of the names together and hide it at the end of the list. Then if a name on the list changes, then so will that value.

I know how to do this in C or Python, but I am rather new to the syntax of VBA.

Kind regards

Rob

This is the value for String in "A1" in "B1".

Please Login or Register  to view this content.

Regards Mick

Excellent, thankyou, this is exactly what I am looking for, I have a spreadsheet with say 20 employees with all dates and stuff, and I am just getting my script to check if there is a change in employee, if there is then go through some other code if not then don't bother. I am trying to avoid going through unneccesary code to speed it up as employees only change every few months from department to department, so this check will flag this up. The chances of one person leaving, then another joining with the same numeric value for his name, in my opinion, is unlikely, but I can do a simple script in matlab that will give me the odds. I just saw this as a quicker way to check this than comparing the list to see if it changed.

I will have a bash at all of these to see which would work best and feedback what I find.

Kind regards

Rob

If you have two lists, one in A1:A4 and one in B1:B4, a formula like:
=SUM(COUNTIF(A1:A4,B1:B4))=COUNTA(A1:A4)
entered as an array formula (confirm with ctrl+shift+enter) will return true/false if the lists are the same/different

Edit, if you need it as vba then you could probably use:
Evaluate("=SUM(COUNTIF(A1:A4,B1:B4))=COUNTA(A1:A4)")

Ok, I just tried your script MikeG, works very well, but the numbers I am getting are just too large, how could I add the individual letters together.
So for example at the moment,
A = 65
AB = 6566

How could I make
AB = 131
?

Thanks in advance.

Rob

Thanks guys, I have lot's to work with now, very much appreciated.

Ok come across another little quirk here. Martin, your formula is perfect except I can't get it to ignore blank squares which is an issue, as I can not now SUM a list of them. Is there a way to do this, I basically have a list of people that grows and shrinks so the formula is permenantly there whether the cell is full or blank, I can't find a way for Excel to ignore this.

@ MickG, I can't see in this code where the value comes from, it is no longer asci text value, this makes my code difficult to verify as a newby programmer, could you point out the line that actually gives a number for a letter please.

Kind regards

Rob