convert or transform a text string into an associated "unique" numeric code (1 Viewer)

[90405]

Is it possible to convert or transform a text string into an associated "unique" numeric code, I have seen many examples of the inverse process, but few regarding this option.


The origin of the text data is company names, so at times it may appear as an alphanumeric string, but the resulting conversion must correspond to a "unique" code, as mentioned for use as a "Primary Key" in Access.

spaces & nonalphanumeric characaters contained in original company data have already been removed.


Examples:

3PRappresentanzaCommercioEsteroSRL
44GattiDiScandroglioMartaAlziatiDSnc
900UomoSRL
2ElleDiLapadulaLucia
CoRe1920SPA
IndustriaMinuterieMetallicheGerosaGBSrl



Please keep all coding simpe, as I am a novice in functions, subroutines etc.

thanking you in advance

 

[KenHigg]

Just curious, what would be the advantage to this method as opposed to using an autonumber primary key?

???

 

[90405]

auto-number would be an alternative, except I prefer to personalise the primary key if possible.

 

[KenHigg]

Have you considered the down sides to this method?

 

[90405]

to convert the text string into a "unique" numeric code

The duplicate problem, is not a priority for the moment, I am more interested in a solution to convert the text string into a "unique" numeric code suitable to be used for a Primary Key in Access

 

[KenHigg]

I suppose you could do a function to pull out all the ascii codes for all the chr's in the string and add them all together.

Doe this sound like it would work?

???

 

[KenHigg]

Here is a bit of code I wipped up, kind of sloppy, but maybe you'll get some ideas:

Public Function f_convertString(myString As String) As Integer
Dim t_intStringLength As Integer
Dim t_counter As Integer

t_intStringLength = Len(myString)

For t_counter = 1 To t_intStringLength
f_convertString = f_convertString + Asc(Mid(myString, t_counter, 1))
Next t_counter

End Function

 

[90405]

I tested it on the raw data, but apparently do not get consistent results taking into account obvious differences in characters:

ApisSRL
ApisSrl
ApisSrl
ApisSrl
ApisSrl

corresponding to
639
703
638
702
702


or:

3PRappresentanzaCommercioEsteroSRL
3PRappresentanzaCommercioEsteroSRL
3PRappresentanzeCommercioEsteroSrl

1081
853
3410

or

AFiorDiPelle
AFiorDiPelle
AFiorDiPelleDiReginatoSonia
AFiorDiPelleDiReginatoSonia

874
427
1136
1136

 

[KenHigg]

You may have to trim() and lcase() it.

 

[90405]

I have already removed any non alphanumeric characters, but lower case might be influencing it?

I'll try your suggestion with lower case

thankyou for the effort

 

[ReAn]

Simple warning:

If your just adding the ascii values together then 'ABC' and 'CBA' will produce the same result.

65+66+67 = 198 = 67+66+65

Here's just a suggestion, i havent tested it yet:

Simple function as such:

key = Sum(a(Len(string1))
where
a(x) = CInt( CStr(asc(mid(string1, x-1, 1))
0) & CStr(asc(mid(string1, x, 1))) )

im unsure if you can follow this, if not let me know, ill actually write the function.

 

[90405]

Thank you for the warning, but I was thinking of extracting every odd digit (the digit in the 1st, 3rd, 5th, 7th etc position) and not adding them, but selecting the actual number in that position?

worth it? to create a unique numeric code (maybe 10 digits in length)

 

[ReAn]

Not Quite sure i follow.

 

[90405]

You may have to trim() and lcase() it.

Could you explain your code, since the results return only 4 digits maximum?

thanks

 

[90405]

Not Quite sure i follow.

for example:

a company name such as:

ApisSRL

would be converted into a numeric code ????

and I would extract (select) digits from this numeric code, derived from every odd positioned digit

and then recombine it


0-6-5-1-1-2-1-0-5-1-15083082076
I get from above odd positions

deriving a unique value
0511510380

I want to use maximum 8 - 16 digits

 

[ReAn]

I understand what you mean by extracting the odd digits, but i don't exactly know what you mean by taking thier numbers and combining.

Anyway, try this: ive tested it, it provides pretty good results, im still unsure as to if it will produce duplicates.

Function MakeCode(myString As String) As Double
  Dim MyDbl As Double, i As Integer
  MyDbl = Asc(Left(myString, 1))
  If Len(myString) > 1 Then
   For i = 2 To Len(myString)
     MyDbl = MyDbl + GenKeyCode(myString, i)
   Next
  End If
  MakeCode = MyDbl
End Function

Function GenKeyCode(myString As String, pos As Integer) As Double
  GenKeyCode = CDbl(CStr(Asc(Mid(myString, pos - 1, 1))) &   CStr(Asc(Mid(myString, pos, 1))))
End Function

Results:

ApisSRL - 310638
ApisSRL - 310638
ApisSrl - 491202

 

[KenHigg]

What about this one:


Public Function f_convertString(myString As String) As String

Dim t_intStringLength As Integer
Dim t_counter As Integer

myString = Trim(UCase(myString))

t_intStringLength = Len(myString)

For t_counter = 1 To t_intStringLength

f_convertString = f_convertString & Asc(Mid(myString, t_counter, 1))

Next t_counter


End Function

 

[ReAn]

Just tested mine on every 3 letter string, found duplicates.

Yours will make hella big numbers.

 

[KenHigg]

Yeah, big numbers indeed. All you can do is integers or strings...

I really fail to see any advantage to doing this anyway. Why not use autonumbers...

 

[ReAn]

Probably because he wants the ability later to enter the company code and it spits out the ID in the table that he should reference, once again pointless because you can just " SELECT * FROM table WHERE name Like 'namehere' " but still, it's an interesting challenge for my brain, and might come in handy here for my workplace.