Convert "text character" into unique values

**Temjin** · 02-02-2010, 03:28 AM

Hello all,

I've done alot of searches already and still couldn't find a solution to my unique problem.

I need to convert a particular cell of "text" into a value based on the sum of the individual characters on alpha order. Some cells would contain spaces, numbers and common symbols too.

For example,

"ABC" will convert to "6" (1+2+3)

or

"AB BA 3" will convert to "9" (1+2+2+1+3)

or

"AB/C" will convert to "6" (1+2+0+3) (anything other than text/number is 0)

Is this even possible?

My problem is that I need to identify each particular cell with an unique number, and these cells have unique texts including numbers/symbols.

Please help.

Thanks!

**DonkeyOte** · 02-02-2010, 03:45 AM

Is it a 1-26 scale for the Alphas or do you wish to adjust ?

We need only consider addition ?

Can the digits that appear occur multiple times and at any given point within a string ?

Will the numeric values always be between 0-9 or would you have 12, -3 etc... ?

The more examples your provide the better in truth... a sample file with all possible variants and expected results would be ideal.

**JBeaucaire** · 02-02-2010, 05:56 AM

If you're trying to create some sort of checksum, what is the intended use of the output? I ask because the values you give to each digit can lead to identical results from two separate text strings.

If "A" = 1 and "B" = 2, then all of these text strings give the same result:

AAAA
BB
AAB

So, what happens in this situation?

**Temjin** · 02-03-2010, 12:53 AM

Hi,

JBeaucaire, YES, I couldn't get into my head to use the word "checksum".

That's what I want.

I want to identify each cell uniquely with a checksum type function. I haven't been able to find one on the net yet that can do both text and numbers at the same time. (and including common symbols)

For more details, I actually have a table with up to 25k+ rows and 4 columns of data. (i.e. 4 cells of data with text and number per row that identifies it)

There is no unique identifier for each row at this time.

There are exactly 717 rows of data that are exact duplicate of each other, and I want to filter the entire table so that only the duplicate rows remain (717 x 2).

The checksum must work on all 4 columns of data as there are certain rows that have identical values in 3 of the 4 columns.

I would then simply use the CountIF($A1:$A1000,A1)>1 formula to find if there were duplicates of the checksum value that identify the row.

I hope this can be done.

Many thanks!!!

**shg** · 02-03-2010, 01:11 AM

Why not just concatenate the four columns and use that as the 'checksum?'

**Temjin** · 02-03-2010, 01:44 AM

YYYEEESSS! Got it working!

Wow, such a simple solution.

I never knew such a function exist!

Thanks a ton again, love this place.

Cheers,

Temjin

**contaminated** · 02-03-2010, 02:26 AM

Hi can you try this UDF

Please Login or Register  to view this content.

Then say in cell A1 pur ABBBA 3 and in B1 this formula

Please Login or Register  to view this content.

Answer would be 11

**JBeaucaire** · 02-03-2010, 12:32 PM

Contaminated, try running your UDF against these two strings:

dogs I hate.
I hate dogs.

They result in the same thing, thus it's not a usable checksum. A true checksum only results in the exact same output when the cells are exactly the same.

**shg** · 02-03-2010, 05:44 PM

A true checksum only results in the exact same output when the cells are exactly the same.

If that were true, JB, checksums would need to be the same length as the data. Parity is a single-bit checksum, right?

I agree that a useful checksum shouldn't succumb to something as simple as the example.

**JBeaucaire** · 02-03-2010, 06:31 PM

Originally Posted by shg

If that were true, JB, checksums would need to be the same length as the data. Parity is a single-bit checksum, right?

Duh, my bad, yes, that could be true. I've had limited experience with checksums, and all the ones I've had to verify were much longer values...6-8 digit answers. But of course, some could result in single digit results.