If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > convert a text string into a "unique" numeric code

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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???
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Location: Aberdeen, Scotland
Posts: 1,067
sure ill look at tommorrow for you
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Jul 2004
Posts: 11
Need yoor email?

Thanks
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Location: Aberdeen, Scotland
Posts: 1,067
Click on my username then you can send me an e-mail via that
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On