Convert file from column to columns using delimiter

[Gene Augustin]

I have a file that has over 2000 entries in column A. Several lines form a
group, separated by a line with " ^ ". I would like to restructure on either
the same page or a new page with the values from the first row after the ^
to the next ^ transposed.
Here's a sample of the data. Most of the groups have 6 rows (not counting
the ^).

D02/19/2008
T0.05
PMI Bank
MInterest
LBank Charges
^
D02/28/2008
T200.00
CX
PGene
MOpening Deposit
LShort Term Loan
^
D03/07/2008
T-93.84
CX
PBank Charge
MPrint Checks
LBank Charges
^
D04/01/2008
T340.26
CX
PPavillion
MService
LWiFi
^
D04/03/2008
T5,004.03
CX
PGene
MLoan
LShort Term Loan
^

Here is what the first four groups should be after translation:



D02/19/2008 T0.05 PMI Bank MInterest LBank Charges ^
D02/28/2008 T200.00 CX PGene MOpening Deposit LShort Term Loan ^
D03/07/2008 T-93.84 CX PBank Charge MPrint Checks LBank Charges ^
D04/01/2008 T340.26 CX PPavillion MService LWiFi ^
D04/03/2008 T5,004.03 CX PGene MLoan LShort Term Loan ^


Gene Augustin

 

[Gary''s Student]

Try this macro:

Sub reConfigure()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
i = 1
j = 1
For k = 1 To n
v = Cells(k, 1)
If v <> "^" Then
s2.Cells(i, j).Value = v
j = j + 1
Else
i = i + 1
j = 1
End If
Next
End Sub

The code assumes that the data is in column A of Sheet1. Adjust to suit
your needs.

 

[Gene Augustin]

Thanks Gary, works like a charm.