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

[snezana]

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:
[TABLE="width: 108"]
<colgroup><col></colgroup><tbody>[TR]
[TD]USER NAME 1[/TD]
[/TR]
[TR]
[TD]USER COMMENT[/TD]
[/TR]
[TR]
[TD]Time stamp[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]USER NAME 2[/TD]
[/TR]
[TR]
[TD]USER COMMENT[/TD]
[/TR]
[TR]
[TD]Time stamp[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]USER NAME 3[/TD]
[/TR]
[TR]
[TD]USER COMMENT[/TD]
[/TR]
[TR]
[TD]Time stamp[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]USER NAME 1[/TD]
[/TR]
[TR]
[TD]USER COMMENT 1[/TD]
[/TR]
[TR]
[TD]TIME STAMP[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]USER NAME 2[/TD]
[/TR]
[TR]
[TD]USER COMMENT 2[/TD]
[/TR]
[TR]
[TD]TIME STAMP[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]USER NAME 3[/TD]
[/TR]
[TR]
[TD]USER COMMENT 3[/TD]
[/TR]
[TR]
[TD]TIME STAMP[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]USER NAME 4[/TD]
[/TR]
[TR]
[TD]USER COMMENT 4[/TD]
[/TR]
[TR]
[TD]TIME STAMP[/TD]
[/TR]
</tbody>[/TABLE]

DESIRED:
[TABLE="width: 500"]
<tbody>[TR]
[TD]USER NAME 1[/TD]
[TD]USER COMMENT 1[/TD]
[/TR]
[TR]
[TD]USER NAME 2[/TD]
[TD]USER COMMENT 2[/TD]
[/TR]
[TR]
[TD]USER NAME 3[/TD]
[TD]USER COMMENT 3[/TD]
[/TR]
[TR]
[TD]USER NAME 4[/TD]
[TD]USER COMMENT 4[/TD]
[/TR]
</tbody>[/TABLE]

 

[NeonRedSharpie]

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

 

[snezana]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]USER NAME 1[/TD]
[TD]USER COMMENT 1[/TD]
[TD]TIME STAMP[/TD]
[/TR]
[TR]
[TD]TIME STAMP[/TD]
[TD]USER NAME 2[/TD]
[TD]USER COMMENT 2[/TD]
[/TR]
[TR]
[TD]USER COMMENT 3[/TD]
[TD]TIME STAMP[/TD]
[TD]USER NAME 3[/TD]
[/TR]
</tbody>[/TABLE]
and it repeats this order continuously (so 1 in 3 is perfect already, the other two not yet ).

Any idea what this could be?

 

[NeonRedSharpie]

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"

 

[snezana]

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!