Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Location
    Adelaide, South Australia, Australia
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    convert or transform a text string into a numeric

    convert or transform a text string into an associated "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, 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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: convert or transform a text string into a numeric

    I am not completely clear on exactly what you want to do. The UDF (User Defined Function) will return a unique number from the string passed to it.

    <pre>Public Function ConvertToNum(strName As String) As String
    Dim I As Long
    For I = 1 To Len(strName)
    ConvertToNum = ConvertToNum & Asc(Mid(strName, I, 1))
    Next I
    End Function
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: convert or transform a text string into a numeric

    Not sure how you want to accomplish getting a unique number for each text string. Here are a couple of ideas

    1. In Excel make a table. In col A enter the text strings - your examples ( starting lets say with A10).
    Select the list and NAME it "CompanyList" (no quotes)
    In cell D1 ( somewhere other than the "List") write: "Company Name"
    In cell D2 enter one of the names from your list
    In cell D3 enter the formula: =MATCH(D2,CompanyList,0)
    This will give you the unique position or rank each occupied by you entry in the list.

    2. However, I think you may find it easier to just enter in your ACCESS table Field1 as the Primary Key with AutoNumber as the type followed by CompanyCode (your list of examples) ,selecting No Duplicates to make sure each is unique.

    Hope this helps

Posting Permissions

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