Convert one column of long data into multiple columns based on a specific string of text

[myawan]

Hi experts,

I have a long list of data exported into excel from a text file. This file contains output of a command from multiple network elements.
Whenever there is a text string 'Welcome to', the below mentioned text should be copied to a new column till the next 'Welcome to' appears. In this case I will have separate column for each network element.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD] --------------------------------------------------[/TD]
[/TR]
[TR]
[TD]MINI-LINK Traffic Node Command Line Interface[/TD]
[/TR]
[TR]
[TD]--------------------------------------------------[/TD]
[/TR]
[TR]
[TD]Welcome to DF0002[/TD]
[/TR]
[TR]
[TD]Password: ********[/TD]
[/TR]
[TR]
[TD]DF0002>s h o w v l a n [/TD]
[/TR]
[TR]
[TD]vlan1[/TD]
[/TR]
[TR]
[TD]vlan2[/TD]
[/TR]
[TR]
[TD]vlan3[/TD]
[/TR]
[TR]
[TD]--------------------------------------------------[/TD]
[/TR]
[TR]
[TD]MINI-LINK Traffic Node Command Line Interface[/TD]
[/TR]
[TR]
[TD]--------------------------------------------------[/TD]
[/TR]
[TR]
[TD]Welcome to DF0003[/TD]
[/TR]
[TR]
[TD]Password: ********[/TD]
[/TR]
[TR]
[TD]DF0003>s h o w v l a n [/TD]
[/TR]
[TR]
[TD]vlan4[/TD]
[/TR]
[TR]
[TD]vlan5[/TD]
[/TR]
</tbody>[/TABLE]

The output should be like this

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Welcome to DF0002
[/TD]
[TD]Welcome to DF0003[/TD]
[/TR]
[TR]
[TD]Password: ********[/TD]
[TD]Password: ********[/TD]
[/TR]
[TR]
[TD]DF0002>s h o w v l a n [/TD]
[TD]DF0003>s h o w v l a n [/TD]
[/TR]
[TR]
[TD]vlan1[/TD]
[TD]vlan4[/TD]
[/TR]
[TR]
[TD]vlan2[/TD]
[TD]vlan5[/TD]
[/TR]
[TR]
[TD]vlan3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]--------------------------------------------------[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MINI-LINK Traffic Node Command Line Interface[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]--------------------------------------------------[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

Sub myawan()
   Dim Ar As Areas
   Dim i As Long
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Replace "Welcome", "=xxxWelcome", xlPart, , False, , False, False
      Set Ar = .SpecialCells(xlFormulas, xlErrors).Areas
      .Replace "=xxxWelcome", "Welcome", xlPart, , False, , False, False
   End With
   For i = 1 To Ar.Count - 1
      Range(Ar(i), Ar(i + 1).Offset(-1)).Copy Cells(1, i + 1)
   Next i
      Range(Ar(i), Ar(i).End(xlDown)).Copy Cells(1, i + 1)
End Sub

 

[myawan]

Genius! It worked

Thanks a ton!

 

[Fluff]

You're welcome & thanks for the feedback