convert a text string into a "unique" numeric code

990405 · #1 (**permalink**) 07-19-04, 09:34

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

DavidCoutts · #2 (**permalink**) 07-19-04, 10:59

What you sound like you are doing is setting up a primary key for the info
and would be far better to put this data into access and define a primary key in a table which lists unique entries for companies
ive put together an excel function for doing this and for 1 value the ascociated code is

05108008209711211211410111510111011609711012209706 71111091091011140991051110691151161011141110830820 76

this is a conversion to ASCII
If you want me to post the conversion code up i will do but thew results are fairly meaningless

Another idea might be to create an Advanced filter on your company names and looking for distinct values add a number next to them and use this as a VLOOKUP Table

HTH

David

990405 · #3 (**permalink**) 07-19-04, 11:15

yES, i WOULD BE INTERESTED IF YOU COUL DSUPPLY THE CODE.

pREFERABLE, i WAS LOOKING AT A CODE NOT LONGER Than 8 - 16 digits

thanks

DavidCoutts · #4 (**permalink**) 07-19-04, 11:49

A unique conversion code won't work for a 8 to 16 number as with 10 seperate Letters only we have 26^10 = 141167095653376 different possibilities, if we include numbers as well we have 36^10 = 3656158440062980 different possibilities this discounts that you need more than 1 number to indicate each letter

here is the code i worked out

Code:

Function ConvertText(ByVal strIn As Range)
    Dim LenText As Integer
    Dim InputData As String
    Dim CharSplit As Variant
    Dim I As Integer
    Dim Output As String
    
    
    InputData = strIn.Value
    LenText = Len(InputData)
    
    ReDim CharSplit(LenText) As Variant
    
    CharSplit(1) = Left(InputData, 1)
    
    For I = 2 To LenText
        CharSplit(I) = Right(Left(InputData, I), 1)
    Next I
    
    For I = 1 To LenText
        Output = Output & Left("000", 3 - Len(CStr(Asc(CharSplit(I))))) & CStr(Asc(CharSplit(I)))
    Next I
    
    ConvertText = Output
End Function

HTH
David

990405 · #5 (**permalink**) 07-19-04, 11:59

I inserted your function into a module

butthen when I input it into a cell
I got an error
#NAME?

Could you suggest what it relates to?

DavidCoutts · #6 (**permalink**) 07-19-04, 12:03

Did you type

=ConvertText(A1) if the value to convert was in cell A1 for example

if you did i will look further into this for you

990405 · #7 (**permalink**) 07-19-04, 12:12

I am still not very proficient with functions etc.

I had type in th ecell the following

=Funtion ConvertText(A1)

when I typed only

=ConvertText(A1) - it worked
****************************

In your opinion, if I know extract the first 10 digits or concatenate a combination of the 1st, 3rd, 5th, 7th, 9th, 11th, etc. (max 10 digits) i.e. every odd digit could this be considered as an acceptable unique code for a Primary Key?

Plausible???

DavidCoutts · #8 (**permalink**) 07-19-04, 13:13

Fraid not, its more than plausible that numbers may be repeated if there are 2 similar entries, It depends on how your data is but somthing like 34onething = 33onething etc. so it's no use for a primary key. you could use it but you could try to determine if 2 of your reduced keys are identical and then handle them somhow or i would prefer summing a group of numbers say every 5 together and then working these out modulo 10

Why don't you use a concurrant sum on each unique identifier instead,

Hold a worksheet with the unique entries have a numberr associated with each entry then you can vlookup each entry from the associated number, It seems like a better soloution to me than having a number generated from and you could add entries which come up as errors to your list of companies, in the same way you would for a DB. but i have to say again if you are doing this kind of thing

hope this makes some sence to you
david

990405 · #9 (**permalink**) 07-19-04, 13:39

Could I send you the worksheet and let you examine the complexities (also requiring removing duplicates in several fields and records) involved and suggest a solution?

DavidCoutts · #10 (**permalink**) 07-19-04, 14:03

sure ill look at tommorrow for you

990405 · #11 (**permalink**) 07-19-04, 14:23

Need yoor email?

Thanks

DavidCoutts · #12 (**permalink**) 07-19-04, 15:54

Click on my username then you can send me an e-mail via that

Internet Services

Web Development

Digital Marketing

Consumer Tech