I've compiled this one:

Sub RetrieveText(TextString, Delimit, InitialCell As Range)
Dim i
Dim LText, Txt

LText = Split(TextString, vbCrLf)
For i = 0 To UBound(LText)
InitialCell.Offset(i + 1, 0) = LText(i)
InitialCell.Offset(i + 1, 0).TextToColumns _
DataType:=xlDelimited, _
other:=True, _
otherchar:=Delimit
Next i
End Sub

'and assume that 'TextString' not in a cell because cannot read vbcrlf in a
cell
--
Regards,

Halim



"mikebres" wrote:

Background: I'm importing data from the web that needs some massaging. Below
is a small part of the data. I want to split this so each part between the |
is in a new cell. To add to this there is another clump of text in the next
cell over that will also need to be split. I want to automate this since I
will also need to remove some special characters in the other clump of text.

Question: I would like to create a UDF that would insert the correct number
of columns after the text, then split it into those new columns. Does
anybody know how to do this?
Thanks
Mike

80401 | 048 | LV80402U.EBF 913 | Fri08/31 07:11
80910 | 010 | LV80935U.EBF 919 | Thu08/30 22:54
81601 | 007 | LV81602U.EBF 913 | Fri08/31 09:08
81601 | 007 | LV81602U.EBF 913 | Fri08/31 09:03
81601 | 007 | LV81602U.EBF 913 | Fri08/31 07:52
80266 | 035 | LV80227U.EBF 919 | Thu08/30 22:13
80266 | 019 | LV80207U.EBF 919 | Fri08/31 05:18
80910 | 005 | LV80915U.EBF 919 | Fri08/31 04:49
80266 | 033 | LV80227U.EBF 919 | Fri08/31 05:10
80266 | 024 | LV80226U.EBF 919 | Fri08/31 04:05
80910 | 014 | LV80903U.EBF 919 | Fri08/31 05:57
80266 | 019 | LV80207U.EBF 919 | Fri08/31 04:22
80266 | 044 | LV80615U.EBF 919 | Fri08/31 03:51
80266 | 024 | LV80226U.EBF 919 | Fri08/31 05:16