transpose a column into many rows

[Guest]

I have a file with one column as follows:

Name
Address
Address2
City, State, Zip

Name
Address
Address2
City, State, Zip

etc.

I need to transpose into columns for mail merge - so transpose the 4 rows
into columns, then go down a line, transpose into columns, etc. so each
address is on a new line. I tried using the TRANSPOSE function, but I can't
get it. Any thought? Thanks!

 

[Myrna Larson]

Assuming your data is in column A, starting a row 2, and all entries have
exactly 4 rows, and there is a blank row between entries,

In B2 put this formula:
=OFFSET($A$2,(ROW()-2)*5+COLUMN()-2,0)
Copy it across through E2.

Then copy the formulas in B2:E2 down through as many rows as needed (the
formulas will begin to return 0's when you reach the end of the data).

Then replace the formulas with their values. Let's say you have 100 names, so
the final list is in B2:E101. Select B2:E101, Edit/Copy, then without changing
the selection, Edit/Paste Special and select the Values option.

Then you can delete the original data in column A.

You can split the city, state, and zip into 3 columns by selecting E2:E101,
Data/Text To Columns and select Delimited with a comma delimiter.