Extracting delimited table field into multi row data

Hello,

I am a new VBA user and was looking for direction on how you can extract data from a multiple column table that has a delimited field into a new table where the delimited value is in individual rows based on the id number.

ie(id: 1 fieldvalues: #one#two#three#four#five#
to ID fieldvalue
1 one
1 two
1 three
1 four
1 five

Thanks.

Copy the following to a code module. You can then use the function in a query or code as required.

Public Function DoSplit(SourceString, ElementNo)
Dim Dest
Dim NoFields
Dest = Split(SourceString, "#", -1, vbBinaryCompare)
NoFields = UBound(Dest)

If ElementNo <= NoFields Then
DoSplit = Dest(ElementNo)
Else
DoSplit = ""
End If
End Function

Thanks for the response, but I stumbled upon the Microsoft parse text example (GetCSWords) a few weeks ago which works perfectly. I'll file your code away for future reference.