Redistributing OR Split Column Info Into Separate Rows

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?

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

Quote:

---

Originally Posted by Excel Fox

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.

Hi

Try this one.

Code:

---

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

---

or

Code:

---

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

---