Results 1 to 5 of 5

Thread: Redistributing OR Split Column Info Into Separate Rows

  1. #1
    Junior Member
    Join Date
    Sep 2014
    Posts
    2
    Rep Power
    0

    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?

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    schorr, can you upload a sample workbook, along with the expected result
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Sep 2014
    Posts
    2
    Rep Power
    0
    Quote Originally Posted by Excel Fox View Post
    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.
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    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

Similar Threads

  1. Split Workbook into Separate Workbooks VBA
    By Admin in forum Download Center
    Replies: 12
    Last Post: 08-08-2018, 09:33 PM
  2. Replies: 34
    Last Post: 03-13-2015, 02:26 PM
  3. Replies: 3
    Last Post: 02-24-2014, 05:48 AM
  4. Replies: 3
    Last Post: 07-29-2013, 11:32 PM
  5. Replies: 12
    Last Post: 08-19-2012, 06:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •