try this and then just delete col a. Compensate if a blank row between each
block. Assumes a header in row 1

Sub transposeem()
x = 2
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row Step 6
Cells(i, 1).Resize(6, 1).Copy
x = x + 1
Cells(x, 2).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next
End Sub


--
Don Guillett
SalesAid Software

"Lisap" wrote in message
...
I have an excel spreadsheet with a column of addresses of different
lengths
like:

Eastfield Business Park
Newark Road
South Glenrothes
Fife
Scotland
KY7 4NS

I need to separate the addresses into separate columns, I've tried using
text to columns but had no luck, is there any other way?

Lisa