Text file --> excel, then specific rows to columns

snezana

New Member
Joined
Sep 4, 2014
Messages
20
Hi all!

Hope someone can help me out with this one:

I receive text files (.txt) with comments made by customers in the following format:
USER NAME 1
USER COMMENT
Time stamp
USER NAME 2
USER COMMENT
Time stamp
USER NAME 3
USER COMMENT
Time stamp

<colgroup><col></colgroup><tbody>
</tbody>
This goes on about a thousand times, with a multitude of users making comments. I want to load this into excel, then I want two columns: one with the user name, one with the user comment. The Time stamp row is irrelevant and I want to remove. I've tried paste special --> transpose (lots of manual steps required...) and 'text to columns' with 'delimited', but there is no specific delimiter that I can use to split these in columns. So, I want to convert these text files containing a huge amount of data in the format as displayed above into 2 columns (one has the user names, one the user comments). Anyone know how to do this? Please see below what the data from text file looks like as pasted into excel, and what is the desired situation.

Greatly appreciated :)

data from text file as pasted into excel:
USER NAME 1
USER COMMENT 1
TIME STAMP
USER NAME 2
USER COMMENT 2
TIME STAMP
USER NAME 3
USER COMMENT 3
TIME STAMP
USER NAME 4
USER COMMENT 4
TIME STAMP

<tbody>
</tbody>

DESIRED:
USER NAME 1USER COMMENT 1
USER NAME 2USER COMMENT 2
USER NAME 3USER COMMENT 3
USER NAME 4USER COMMENT 4

<tbody>
</tbody>
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Code:
Sub makeItPretty()

    For x = 1 To ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Step 3
    
        Cells(x, 2) = Cells(x + 1, 1)
        Cells(x + 1, 1).Clear
        Cells(x, 3) = Cells(x + 2, 1)
        Cells(x + 2, 1).Clear
    Next x
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete
    Columns("A:C").AutoFit
    Range("A1").Select


End Sub
 
Upvote 0
Code:
Sub makeItPretty()

    For x = 1 To ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Step 3
    
        Cells(x, 2) = Cells(x + 1, 1)
        Cells(x + 1, 1).Clear
        Cells(x, 3) = Cells(x + 2, 1)
        Cells(x + 2, 1).Clear
    Next x
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete
    Columns("A:C").AutoFit
    Range("A1").Select


End Sub

First of all thanks for helping me out!! The first one is split perfectly, but after that the order shuffles, like this:

USER NAME 1USER COMMENT 1TIME STAMP
TIME STAMPUSER NAME 2USER COMMENT 2
USER COMMENT 3TIME STAMPUSER NAME 3

<tbody>
</tbody>
and it repeats this order continuously (so 1 in 3 is perfect already, the other two not yet :) ).

Any idea what this could be?
 
Upvote 0
Is there a blank space between TIME STAMP 1 and USER NAME 2? I assumed no. If there is, then change "Step 3" to "Step 4"

Ah, I should have specified the blank space. Awesome that you still saw the solution! It works perfectly now, thanks a bunch, this has helped me A LOT!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top