convert text labels to excel columns

[Guest]

I have seen this posted in another newsgroup but did not understand the
answer. I have copied many names/address and pasted them into Excel. All of
the info is sitting under one column. I would like to find out how to
seperate the info into multiple columns so that I can create labels.

Ex.

Mary Smith
1234 Main St
Suite 123
Anywhere, USA 12345

I would like to put it under multiple headings ex. name address city/state zip

I'm not clear how a delimeter works, The options it shows when I try to use
the text to columns does not show how mine are seperated. It is not a dash
or slash
(-or/) it is a straight up and down line???? Ex. Mary Smith (line) address.
I hope this makes since and someone can help me please.

 

[Dave R.]

Try copying the 4 cells, then go to another cell/column somewhere and do
paste special>transpose. This will paste data that spans 4 columns instead
of 4 rows.

 

[Gord Dibben]

RSF

Data>Text to Columns operates on data that is in one cell and splits it acroos
several in a row.

Looks like your data is in 4 cells in one column. TTC will do you no good in
that case.

If your data is consistent a macro or worksheet Function could be devised to
get the data into columns all on one row per address.

Then TTC could be used to do the final splitting.

If your data is in A1:A4 and is consistently 4 rows as your example, enter
this formula in B2 and drag across to E2 then down until you get zeros.

=OFFSET($A$1,(ROW()-1)*4+COLUMN()-2,0)

Then use TTC to split Column E into two....city/state and zip.


Gord Dibben Excel MVP