Ok this is possible to do with formulas but it will require a lot of them in different columns and it just sounds like a pain. You mentioned you were looking for a slick way to accomplish the above. I cant think of anything slicker than pushing a button and having it all magically happen.

That is what the attached workbook does. I wrote some VBA code to parse out the information and then I have a very simple concatenating formula putting together the name address and phone number. This will work with any number of records and you can even add columns if you need to.

I tested it with the data you provided and it appears to work well. If there are any issues let me know and I will sort them out.

You will have to excuse the old workbook format I have my reasons for using an older version lol.

Thanks for the opportunity to provide solutions.

Attached Files

Address Parse.xls (32.5 KB, 19 views)