In addition to Max's suggestion, you may want to break up the City,State,Zip
cell into 3 distinct cells after you get the 4 rows into the one row format.

Select that column and DataText to ColumnsDelimited by comma and Finish.

Most labelling programs like Word work better with those in separate columns.


Gord Dibben Excel MVP

On Fri, 16 Dec 2005 22:26:35 +0800, "Max" wrote:

Assuming the source data is in A1 down,

Put in B1: =INDEX($A:$A,ROW(A1)*4-4+COLUMN(A1))
Copy B1 across 4 cols to E1, fill down until zeros appear
signalling exhaustion of data from col A

If required, freeze the results in cols B to E with an in-place:
Copy Paste special check "Values" OK
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"PM" wrote in message
...
I have addresses in an Excel spreadsheet. Here is how they are formatted:

row 1: (blank)
row 2: name
row 3: address
row4: city, state, zip

This then repeats about 700 times.

Can anyone help me create a formula that copies these 4 rows to a single
row
in another tab? For example, row1 would go to columnA in the next tab,
row2
would go to columnB in the next tab, etc? I'd need it to repeat each
time,
so I have a tab that has each record in a single row (for printing
labels).

Please help! I cannot figure out the formula.

PM