I Hope I'm just overlooking a stock standard way to do this, but:

I'd like to find a fairly simple way to convert text in a cell to a
numerical value, that I can then use as a checksum.
I don't mean cells containing the word"one" would equal 1, but rather
something like ("o"=15)+("n"=14)+("e"=5)=34
I want to use it to help uniquely identify rows, so it has to be
simple, or it will take too long to execute and be counter productive.

Thanks,
Kestrel


--
Kestrel1492


------------------------------------------------------------------------
Kestrel1492's Profile: http://www.excelforum.com/member.php...o&userid=24849
View this thread: http://www.excelforum.com/showthread...hreadid=385637

Kestrel1492 Wrote:
I Hope I'm just overlooking a stock standard way to do this, but:

I'd like to find a fairly simple way to convert text in a cell to a
numerical value, that I can then use as a checksum.
I don't mean cells containing the word"one" would equal 1, but rather
something like ("o"=15)+("n"=14)+("e"=5)=34
I want to use it to help uniquely identify rows, so it has to be
simple, or it will take too long to execute and be counter productive.

Thanks,
Kestrel

An easy way (as I see it) is to enter your addends in different cells.
For example,

A1 contains "o"=15
A2 contains "n"=14
A3 contains "e"=5

and then, you can enter this formula in

Cell B1: *=value(right(A1,len(A1)-find("=",A1)))*

Next step is to copy the formula in B1 down to B3. In cell B4, you can
enter this formula

=SUM(B1:B3)

to get your desired result.

I hope I interpreted your problem correctly.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=385637

Sorry,
For clarification, each cell may contain as much as a full paragraph of
text. An example of a cell would be:

WHEN IN RAIN ALL RADIOS ARE UNREADABLE EXITING RAIN XMISSIONS TO THE
A/C ARE READABLE

Sorry, I should have said these are aircraft log entries, and sometimes
long winded.

Thanks,
Kestel


--
Kestrel1492


------------------------------------------------------------------------
Kestrel1492's Profile: http://www.excelforum.com/member.php...o&userid=24849
View this thread: http://www.excelforum.com/showthread...hreadid=385637

If you put some single characters in column A and this in column B:

=code(a1)
(and drag down)

You'll see that each character has a numeric representation.

A = 65
a = 97
.. = 46

Maybe you could use those codes to get your checksum.

=SUMPRODUCT(CODE((MID(A1,ROW(INDIRECT("1:"&LEN(A1) )),1))))

or maybe better:

=IF(A1="","",SUMPRODUCT(CODE((MID(A1,ROW(INDIRECT( "1:"&LEN(A1))),1)))))

Note that these will evaluate the same:

ABCD
DBCA
ACBD
(and so forth)

But maybe it's close enough???

============
Are you worried about having duplicates in your data?

If yes, you may want to read Chip Pearson's treatise on Duplicates:
http://www.cpearson.com/excel/duplicat.htm


Kestrel1492 wrote:

I Hope I'm just overlooking a stock standard way to do this, but:

I'd like to find a fairly simple way to convert text in a cell to a
numerical value, that I can then use as a checksum.
I don't mean cells containing the word"one" would equal 1, but rather
something like ("o"=15)+("n"=14)+("e"=5)=34
I want to use it to help uniquely identify rows, so it has to be
simple, or it will take too long to execute and be counter productive.

Thanks,
Kestrel

--
Kestrel1492

------------------------------------------------------------------------
Kestrel1492's Profile: http://www.excelforum.com/member.php...o&userid=24849
View this thread: http://www.excelforum.com/showthread...hreadid=385637

--

Dave Peterson

Ah, nice.

I knew there had to be some kind of value function, Thanks Dave.


--
Kestrel1492


------------------------------------------------------------------------
Kestrel1492's Profile: http://www.excelforum.com/member.php...o&userid=24849
View this thread: http://www.excelforum.com/showthread...hreadid=385637