Macro to convert mailing address list into excel table

**ravikiran** · 10-13-2010, 06:06 PM

Hi,

I have mailing addresses in an excel file (Please check the file attached).

I need to split the address into the following fields and write to the table: (please check "final" work sheet in the file attached)
First name, Last name, Company, Address, City, State, Zip Code, email address, phone number1, phone number 2, phone number 3

There are few complexities like:
some addresses has more than one phone number. Some doesn't have the company name.

Please help me in creating a macro or formulas to be able to create a table with the available data.

Thanks in advance,

Ravi.

**Marcol** · 10-13-2010, 09:37 PM

This can be done with VBa and formula, (probably using workbook functions in VBa).

If the data is grouped in blocks of 10, (a VBa or manual operation), then the formulae applied as in the attached workbook, you have a result.

However there is a complete anomally in your data that I can't see a way around, the block and row highlighted in yellow.

If this is a rare occurance it could easily be corrected manually, otherwise we have a problem.

I'm not familiar with USA zip codes, but if this is wrong, it is easily corrected, given the correct rule.

Please confirm that this potential solution is acceptable before I proceed further.

**ravikiran** · 11-04-2010, 02:00 AM

Yes Macrol, I just observed that anomoly. There are few instances in which this field is present.

I need to check if I can do another export of the data.

Can you please send me the macro.

Thank you.

**Marcol** · 11-04-2010, 07:30 AM

Okay.
Best I can do within the limits of the data supplied.

1/. I have added conditional formatting to sheet "Final" to detect some errors.

2/. If after importing your data any row in sheet "Final" shows yellow, then run this macro

Please Login or Register  to view this content.

3/. Ihave retained the formulae with some slight modification, these could be improved, but if they work leave them as they are.

I suspect that you will probably want to delete sheet "Data" and have only values in sheet "Final"

To best acheive this I would Copy and PasteSpecial > Values sheet "Final" to a new sheet then export that sheet to a new workbook, retaining this workbook as a template.

Hope this helps