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.

--------------------------------------------------

MINI-LINK Traffic Node Command Line Interface

--------------------------------------------------

Welcome to DF0002

Password: ********

DF0002>s h o w v l a n

vlan1

vlan2

vlan3

--------------------------------------------------

MINI-LINK Traffic Node Command Line Interface

--------------------------------------------------

Welcome to DF0003

Password: ********

DF0003>s h o w v l a n

vlan4

vlan5

<tbody>
</tbody>

The output should be like this

Welcome to DF0002	Welcome to DF0003
Password: ********	Password: ********
DF0002>s h o w v l a n	DF0003>s h o w v l a n
vlan1	vlan4
vlan2	vlan5
vlan3
--------------------------------------------------
MINI-LINK Traffic Node Command Line Interface
--------------------------------------------------

<tbody>
</tbody>

 

[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