Advanced Text To Columns

Hi,

I have a spreadsheet that has a whole set of address data in each cell for quite a few rows. I cannot figure out how to get it separate out into separate columns for address #1 (the location), address #2 (the street address), city, state, and zip. The way the data is in each cell is not consistent with the spacing.

I've attached the spreadsheet I'm trying to work with. I'm referring to column D (it was easy enough to separate out the info that is now in column C).

Thanks so much!

Michael