Insert columns and split text
 

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

Insert columns and split text
 

I would start a new workbook.
Start recording a macro.
Open your text file (into a new workbook's worksheet)
Parse your data (delimited by |)
Copy columns
To the other workbook
Insert the copied columns where you want
(shift cells right or insert new empty columns before you paste)

Close the text file you just opened

And stop recording the macro.

Save this workbook with the macro (it could be in the same workbook with the
existing data--or a separate workbook).

If it's in a separate workbook, just open that workbook and then
Tools|Macro|macros...
and run your macro to test it.



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

--

Dave Peterson

Insert columns and split text
 

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