convert a text string into a "unique" numeric code

990405 · Jul 19th 2004

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.

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

philby · Jul 20th 2004

Hi ??????? - and welcome to the forum.

Hope this is what you are after, I got this idea from some code on this forum ages ago that changes the chr type used to provide password protection. So I can't take the credit. The idea is good though it allows you to convert any type into ASCII and then Chr$ so when someone looks at the data it looks like rubbish.

Code

Private Sub CommandButton1_Click()
Dim counter As Integer
Dim clientname As String


   ' setting up the string
   clientname = Range("I25").Text
    ' setting the loop - encoding it into different characters
    For counter = 1 To Len(clientname)
        Mid(clientname, counter, 1) = Chr(Asc(Mid(clientname, counter, 1)) _
        + counter)
    Next
    ' showing you the encoded text
    Range("N25").Value = clientname
    
    ' now decoding the string back to origional
  For counter = 1 To Len(clientname)
        If Asc(Mid(clientname, counter, 1)) >= (counter) Then
            Mid(clientname, counter, 1) = Chr(Asc(Mid(clientname, counter, 1)) _
            - counter)
        End If
    Next
  ' showing the origional string value
  Range("N24").Value = clientname
End Sub

Display More

so your client name : 3PRappresentanzaCommercioEsteroSRL
becomes: 4RUeuvym|oy€n|‰qT€zˆzˆ_Ž‚Žssn

and back to : 3PRappresentanzaCommercioEsteroSRL again - will convert all characters regardless of if they are %^$#(* into &`''-0'

I have not set up specific ranges but I'm sure you can adapt to suit.

Hope this helps - Phil

990405 · Jul 20th 2004

I was thinking something different, close but not quite ..
I cannot accept non-aphanumeric characters as a unique identification code.

I was thinking of a unique code 8 - 16 digits

thank you for your effort

Vic

Anonymous · Jul 20th 2004

Since there are 26 letters in the alphabet in addition to the 10 single character integers, there is no way each possible string of text such as 3PRappresentanzaCommercioEsteroSRL can be assigned a unique 16 digit number. What is the end goal of your question? Do you just want to take a certain list of company names and assign them a unique number so that a lookup table can be used to translate back and forth?

990405 · Jul 20th 2004

Essentially yes, any suggestions?

Anonymous · Jul 20th 2004

Why not just use the Autonumber Access assigns to a particular company record in the master company table?

990405 · Jul 20th 2004

I am still a bit of a novice with Access.

Apparently because the indexing/ processing is much more efficient if done on a numeric code and I was tryingto associate a unique company code, which could also be used in other operations.

I am also having a problem in removing duplicates.
as shown by data given below:

AlmasPelliSasDiMassaroAlessandroC
AlmasPelliSasDiMassaroAlessandroEC
AlmasPelliSasDiMassaroAlessandroEC
AlmasPelliSasDiMassaroAlessandroEC

ApisSRL
ApisSrl
ApisSrl
ApisSrl
ApisSrl

AIPASpaAgenziaItalianaPerPubblicheAmministrazioni
AIPASpaAgenziaItalianaPerPubblicheAmministrazioni
AIPASpaAgenziaItalianaPerPubblicheAmministrazioni

AFiorDiPelle
AFiorDiPelle
AFiorDiPelleDiReginatoSonia
AFiorDiPelleDiReginatoSonia

3PRappresentanzaCommercioEsteroSRL
3PRappresentanzaCommercioEsteroSRL
3PRappresentanzeCommercioEsteroSrl

ConfartDiTosanaPAECSAS
ConfartDiTosanaPAECSAS
ConfartDiTosanaPAECSAS
ConfartDiTosanaPAECSAS
ConfartDiTosanaPierachilleECSAS
ConfartDiTosanaPierachilleECSAS

the comapnies can also have other fields which may have duplicates, such as:
product sector
address
telephone

Could you suggest an efficient method to remove duplicates, but retaining the unique record.

I can send you a sample excel file to give you a better understanding if you like.

Any suggestions?

On how to put me on the right track?

Anonymous · Jul 20th 2004

What are you intending be done in Excel and what in Access? In Access there is a built in query that will help you find duplicates, but the nmes have to me identical. Only a human with company knowledge would be able to decide whether AFiorDiPelle
and AFiorDiPelleDiReginatoSonia are two separate companies or the same company. Assigning a unique numeric os short alphanumeric code to each company is efficient for linking, but the code can be arbitrary so a simple sequential assignment as companies are added to the database does as well as anything else.

990405 · Jul 20th 2004

Could you elaborate, as I am trying to understand what you intend?

"simple sequential assignment as companies are added to the database does as well as anything else"???

Anonymous · Jul 20th 2004

In the Access table that has data for the companies (one record for each company) have a field that records the assigned company number. For example, C1 for the first compan, C2 for the second, C1000 for the thousandth. Then you can link this table using the C numbers instead of the company names. I assume that was the purpose of your original question, was it not?

convert a text string into a "unique" numeric code

Participate now!

Share