Separating Street Address City State Zip that only contain spaces

Hey guys,

I am at a loss. Any help would be much appreciated because I have too much data (10K+ rows worth) to even think I could tackle this manually...

Here's the scoop-- complete address info is being crammed into one cell, and I need this data separated into individual columns respectively

SAMPLE:

501 St. Joseph St. P.O. Box 5820 Sturgis Michigan 49081
501 St. Joseph St. P.O. Box 5820 Sturgis Michigan 49081
5217 Old Baumgartner Road St. Louis Missouri 61329
5217 Old Baumgartner Road St. Louis Missouri 61329
203 Enterprise CT Oostburg Wisconsin 57300-1656
660 Innsbruck Drive Buffalo New York 12447
660 Innsbruck Drive Buffalo New York 12447

And the data can vary from there... some without zips, some without street names; however, the majority are like above.

WHAT ON EARTH CAN I DO?? Any help is much appreciated.

Thanks!

Yea man, it's pretty screwy. Text2Columns would serve as an even greater headache...

I can't really go into the source of the data... the only thing I know is that is the best, or worst, it will get.

You can't do it. Well that's not strictly true, but it's a humongous amount of work, you should really be looking at a commercial solution to do this. There are plenty out there and very reasonably priced

I belive there is somewhere on the internet list of US towns and states.
In that case you could have another sheet with those informations.

For example:

Please Login or Register  to view this content.

And then you can extract rest of the address.