Home |
Search |
Today's Posts |
#1
|
|||
|
|||
help needed with text to column
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 |
#2
|
|||
|
|||
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 www.Geocities.com/UDQServices Your "Impossible" Task Could Be Someone Else's "Piece of Cake" ===== * ===== * ===== * ===== "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 |
#3
|
|||
|
|||
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" wrote: 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 www.Geocities.com/UDQServices Your "Impossible" Task Could Be Someone Else's "Piece of Cake" ===== * ===== * ===== * ===== "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 |
#4
|
|||
|
|||
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 www.Geocities.com/UDQServices Your "Impossible" Task Could Be Someone Else's "Piece of Cake" ===== * ===== * ===== * ===== "Lisap" wrote in message ... 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" wrote: 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 www.Geocities.com/UDQServices Your "Impossible" Task Could Be Someone Else's "Piece of Cake" ===== * ===== * ===== * ===== "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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Changing column size w/o losing text | New Users to Excel | |||
Excel formula needed to amounts into one column pulling from 2 col | Excel Worksheet Functions | |||
Search column and move text formula | Excel Discussion (Misc queries) |