Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transposing an Address List
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transposing an Address List
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/Variab...mnToTable.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) On Thu, 14 May 2009 10:28:02 -0700, Hank24 wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transposing an Address List
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. -- Gary''s Student - gsnu200852 "Hank24" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel list of names, addresses and email to address book/contact list??? | Excel Discussion (Misc queries) | |||
Transpose list of address into rows for mailing list | Excel Discussion (Misc queries) | |||
Transposing list of numbers | New Users to Excel | |||
Transposing 4-row clumps of name and address in Column A to Columns A,B,C,D | Excel Worksheet Functions | |||
long address list, name-address-city, listed vertically, how do y. | Excel Discussion (Misc queries) |