help needed with text to column

[Guest]

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

 

[Daniel CHEN]

I assume that your address information are stored in one column and want to
change it to one row.

Try Transpose function

If my assumption is not right, then please be more specific

===== * ===== * ===== * =====
Daniel CHEN

Spreadsheet/VBA Specialist
(e-mail address removed)
www.Geocities.com/UDQServices
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
===== * ===== * ===== * =====

 

[Guest]

I want to separate the addresses so its like

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

with each part of the address in a different column

 

[Daniel CHEN]

seems there is no easy way, since the width of each field varies for
different addresses.
If all the addresses have some pattern, like three blank spaces in the 1st
field, one blank space in 2nd and 3rd fields, ..., then there maybe some
indirect way to solve it.

===== * ===== * ===== * =====
Daniel CHEN

Spreadsheet/VBA Specialist
(e-mail address removed)
www.Geocities.com/UDQServices
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
===== * ===== * ===== * =====

 

[Don Guillett]

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