Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trim text and split across three columns | Excel Discussion (Misc queries) | |||
Text to columns, split at first space only | New Users to Excel | |||
split text into more columns excel | Excel Programming | |||
macro to split text in columns | Excel Discussion (Misc queries) | |||
split text , insert to columns | Excel Discussion (Misc queries) |