Transposing an Address List

[Hank24]

I have a 30k line address list that looks like this:
Customer #1
ATTN: ACCOUNTS PAYABLE
Street Address, SUITE B
CITY, STATE
ZIP CODE
Customer #2
Street Address
City, State
ATTN: ACCOUNTS PAYABLE
Zip Code

I need to move all the information from one column to 5 (transpose the info
so that all the names are in one column, cities in another, etc.). Does
anyone know how I can do that without copying and pasting each address?

 

[Chip Pearson]

It could be automated if there is some method of identifying the start
and end of each address. Do all addresses have the same number of
lines? Is there something that indicates the end of an address? You
might be interested in code I have at

http://www.cpearson.com/Excel/VariableBlockColumnToTable.aspx
http://www.cpearson.com/Excel/ColumnToTable.aspx

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gary''s Student]

Assuming that your data is in column A in fixed blocks of 5 rows each, In B1
enter:

=INDIRECT("A"&ROW()*5-(5-COLUMNS($B:B)))

Then copy B1 to C1 thru F1
Then copy B1 thru F1 down as far as you need.