Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2004
    Posts
    11

    convert a text string into a "unique" numeric code

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  3. #3
    Join Date
    Jul 2004
    Posts
    11
    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

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  5. #5
    Join Date
    Jul 2004
    Posts
    11
    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?

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  7. #7
    Join Date
    Jul 2004
    Posts
    11
    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???

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  9. #9
    Join Date
    Jul 2004
    Posts
    11
    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?

  10. #10
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    sure ill look at tommorrow for you

  11. #11
    Join Date
    Jul 2004
    Posts
    11
    Need yoor email?

    Thanks

  12. #12
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Click on my username then you can send me an e-mail via that

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •