[AccessD] Extract Strings from Long Test String

Jurgen Welz jwelz at hotmail.com
Wed Jul 9 12:51:57 CDT 2008


Sub getParts()    Dim str As String        str = "[LHDMSP]/[AN/FMQ-7]/[7361002-2]/[7361799]/[7361795]/[953]/[7953200]/[7953207]/[1N756A][LHDMSP]/[AN/FRR-95]/[317400]/[317359]/[624459]/[317298-1]/[PTM15-1.5D]/[235-7053P334][LH MWD]/[AN/FPS-123 (V)1]/[957157-1]/[953252-1]/[584786-1]/[STM5-24]/[586371-5][LH SCS]/[AN/FPS-85]/[2054300-0501]/[Receiver]/[No Number(F20)]/[2052660-0502]/[2018219-0501]/[2020306-0501]/[2055347-0506]/[2018880-0001]/[1N752A]"    fnGetParts strEnd Sub
 
Function fnGetParts(strIn As String)    Dim lngI As Long    Dim lngJ As Long    Dim strTest As String
    strTest = strIn    lngI = InStr(strTest, "[")    Do While lngI        strTest = Mid$(strTest, lngI + 1)        lngJ = InStr(strTest, "]")        Debug.Print Left$(strTest, lngJ - 1)        lngI = InStr(strTest, "[")    LoopEnd FunctionCiaoJürgen WelzEdmonton, Albertajwelz at hotmail.com> Date: Wed, 9 Jul 2008 11:52:59 -0500> From: JHewson at nciinc.com> To: accessd at databaseadvisors.com> Subject: [AccessD] Extract Strings from Long Test String> > I'm trying to extract several components from a text string. The string> was created from a legacy system download into Excel. The text string is> located in one field of a table that was imported into the database.> The string is called a "Full Path" because it represents the location> where the data was located in the legacy system. Each import could have> several thousand rows.> > Below are samples of the field (watch wrap):> [LH> DMSP]/[AN/FMQ-7]/[7361002-2]/[7361799]/[7361795]/[953]/[7953200]/[795320> 7]/[1N756A]> [LH> DMSP]/[AN/FRR-95]/[317400]/[317359]/[624459]/[317298-1]/[PTM15-1.5D]/[23> 5-7053P334]> [LH MWD]/[AN/FPS-123 (V)> 1]/[957157-1]/[953252-1]/[584786-1]/[STM5-24]/[586371-5]> [LH SCS]/[AN/FPS-85]/[2054300-0501]/[Receiver]/[No Number> (F20)]/[2052660-0502]/[2018219-0501]/[2020306-0501]/[2055347-0506]/[2018> 880-0001]/[1N752A]> > What I need to do is extract the strings between the brackets (i.e. "[",> "]/[","]") for each "Full Path" and be able to use them in queries.> There should be a minimum of five sets and there could be as many 15> sets of brackets.> > I have been able to get the first two but after that nothing seems to> work.> > Any suggestions?> > MTIA> > Jim
_________________________________________________________________
Find hidden words, unscramble celebrity names, or try the ultimate crossword puzzle with Live Search Games. Play now!
http://g.msn.ca/ca55/212


More information about the AccessD mailing list