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/re...ata-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