Redistributing OR Split Column Info Into Separate Rows

[schorr]

I have a tab delimited text file that I'm trying to import into Excel, however rather than having a large ongoing column I'd like to separate column info after each "%" into rows.

Here's what my text data looks like:

TITLE;IMPRINT;RECORD #(ORDER);LOCATION;FUND;E PRICE;Paid Date;Invoice Date;Invoice Num;Amount Paid;Voucher Num;Copies;Sub From;Sub To;Note
Methods in enzymology.;;o10000768;tgen ;mso ;$0.00;07-09-98;07-03-98;S121348;99.95;18;; - - ; - - ;v291, 1998%09-18-98;08-28-98;s122879;115.00;83;; - - ; - - ;V292%09-18-98;08-28-98;s122879;110.00;83;; - - ; - - ;V293%09-21-98;09-11-98;S123277;99.95;87;; - - ; - - ;V297%09-21-98;09-11-98;S123277;110.00;87;; - - ; - - ;V298%10-22-98;10-02-98;S123932;117.17;152;; - - ; - - ;v296%10-26-98;09-18-98;S123543;99.95;154;; - - ; - - ;v295%11-13-98;11-06-98;S125135;100.87;182;; - - ; - - ;v301%11-13-98;11-06-98;S125135;100.87;182;; - - ; - - ;v299%11-13-98;10-30-98;S124866;93.73;184;; - - ; - - ;v300%11-13-98;11-06-98;S125135;110.99;182;; - - ; - - ;v294%04-27-99;04-16-99;S129959;90.00;448;; - - ; - - ;v302%06-16-99;06-04-99;S131348;105.00;518;; - - ; - - ;v303%06-24-99;06-18-99;S131746;115.00;536;; - - ; - - ;v304%08-04-99;07-30-99;S132640;99.95;598;; - - ; - - ;v306%09-14-99;09-03-99;S133354;105.00;658;; - - ; - - ;v307%09-20-99;09-10-99;S133537;99.95;664;; - - ; - - ;v308%10-11-99;10-01-99;S134096;115.81;699;; - - ; - - ;v309%11-22-99;11-05-99;S135220;100.60;759;; - - ; - - ;v310

Right now info after every "%" ends up in a single column that goes on forever.

I saw a discussion about this that illustrates exactly what I'm trying to do but on a smaller scale. Here's a link to the discussion post: http://www.excelfox.com/forum/f22/redistribute-a-delimited-column-of-data-into-separate-rows-keeping-other-data-as-is-420/

Does anyone have a solution to my problem?

[Excel Fox]

schorr, can you upload a sample workbook, along with the expected result

[schorr]

schorr, can you upload a sample workbook, along with the expected result

Okay I uploaded a file with the run-on row and another file with the desired result. Basically everything after the % I would like to drop to a new line. Preferably starting under the Paid Date field. All the field separated by the % need to repeat under the Paid Date, Invoice Date, Invoice Number, etc... Does this make sense? These are order records so under each title there are multiple payments.

Thanks in advance for your help.

[Admin]

Hi

Try this one.


Option Explicit

Sub kTest()

Dim v, i As Long

Const TextFilePath = "C:\test.txt" '<<< adjust to suit
Const FixDelim = ";;;;;;"

v = Split(CreateObject("scripting.filesystemobject").opentextfile(TextFilePath).readall, "%")

For i = 1 To UBound(v)
v(i) = FixDelim & v(i)
Next

With Range("a1").Resize(UBound(v) + 1)
.Value = Application.Transpose(v)
.TextToColumns .Cells(1), xlDelimited, , , , True
.WrapText = False
End With

End Sub

[snb]

or


Sub M_snb()
c00="G:\OF\example.txt"

with creatobject("scripting.filesystemobject")
.createtextfile(c00).write replace(.opentextfile(c00).readall,"%",vbcrlf)
end with

workbooks.open c00
End Sub