Moving Data From One Cell To Many

I have a worksheet with roughly 7,000 rows on it where the data is all crammed in column A. It just sits on top of each other with a line break. I added in the

Please Login or Register  to view this content.

function, but when there is a line break it rams the data into each other with no break. For example my data currently looks like this

Please Login or Register  to view this content.

When I use Clean() it puts the data like this Blue Bell1122 Red Light Stop SignSante Fe, NM 22222. Question at hand -> is there a worksheet function or even VBA that would parse out the Name, Address, City, State and zip that could move them all into there own individual cell?

The data always comes in this format, the only difference is sometimes there is no zip code, but the layout is exactly the swame.

@leelnich - I ran your macro and it works for about 95% of the cases. I discovered that in the 5% of the cases that it throws an error there is an extra line at the bottom of garbage data so those problems read like

Please Login or Register  to view this content.

Is there a way to ignore the 4th line if it exists?

if your third row is ALWAYS city/state/zip, then this will work. If you have any valid fourth rows, I'll have to find a way to distinguish good from bad eg. Are there ever any NUMERALS in the discardable text?

Please Login or Register  to view this content.

I'm signing off, I'll check back tomorrow about 10 am. Take care- Lee
ps. I lived in Atlanta for years. Loved it.

No the 4th row is always text and is always discardable (garbage data).