Problem exporting multi-line addresses to Excel

[mjwillyone]

I exported a table from MS Access 2007 to Excel. In Excel, I would like a way to transpose all multi-line addresses in the address1 column into other columns. A 3-line address will take up 3 columns, a 4-line will take up 4. Is there a way to do this?

I have attached a file that I think will help.

Thank you,


MikeAddresses from Access 2007.zip

[ssanfu]

Is there a way to do this?

What is the table structure? If the table has one field for the multi-line addresses, your table structure is not normalized.
1) Use a query to get the multi-line addresses into the proper columns. Then export the query.

2) Write code in Excel to parse the cell into 2 or 3 more cells.

[NTC]

it requires a certain symbol as a separator between lines or that each part have a fixed width.....

[mjwillyone]

Yes .. the symbol is what I cannot see to know how to remove it...

[NTC]

you will need to look up String manipulation functions/methods available in VBA; here is a list: InStr Left Right Mid and Len

using a query; to begin you have a calculated field that identifies the character count to the first symbol and then displays just characters to the left; then you make a 2nd calculated field that begins to the right of the first symbol and finds the next symbol and displays the characters in between; so on and so forth - - you'll need at least 4 calculated fields if there are a max of 4 address lines - potentially more as you may need some interim fields to display to aid your view of the data.

It is do-able. Takes a bit of time to get it right. But in the end you will end up with a query that has chopped the address into separate fields. Then this is what gets exported to excel.