Need to Parse Delimited Text Field

[JonWolgamuth]

I have a database here at work (not developed by me) where there is a text field that is laid out thusly:

Undelimited_Field
LLL-LLLL-3
LLLLL-LL-5
LL-LLLLLLL-19

I need to be able to parse the field into 3 fields, the last one being a number field.

Is there a quick and easy way to create a recordset based on this field using the dash as a delimiter? It sounds easy, but so far I am stumped.

Humbly,

jon

 

[Remou]

Hi
If you choose Get External Data, Import and select Text Files, you can choose a delimiter. You can then save the specification for use with Transfertext in code.

 

[Trevil]

To split the fields, you could do the following:

Dim astrFields() As String
Dim i As Integer
astrFields = Split(YourString, "-")
For i = 0 To UBound(astrFields)
Debug.Print "Field " & i & vbtab & astrFields(i)
Next i



"Hmmm, it worked when I tested it....

 

[PHV]

If ac2k or above, you may try something like this:
SELECT Left(yourField, InStr(yourField,'-')-1) As Part1
, Left(Mid(yourField,InStr(yourField,'-')+1), InStrRev(yourField,'-')-InStr(yourField,'-')-1) As Part2
, Mid(yourField, InStrRev(yourField,'-')+1) As Part3
FROM youTable
WHERE yourField Like '*-*-*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886