Text to Columns With Inconsistent Data

Hello,

I searched for this issue but couldn't find it. I know it's bound to come up often.

Anyway, what I have is a column with rows of data which is not consistent throughout the file. Text to columns will break it up based on a space delimited, but my issue is that not every row is structured alike. For instance, I'm working with geographical locations. I have city names, state abbr., zip codes, followed by a company name. The problem is some cities have two names, such as "Idaho Falls". In another row it could be "Reno"...ir could even be spelled in all caps, like "RICHMOND".

I've attached a spreadsheet with an example of what I have and what I want. I believe I have covered each different row scenario. It even has Canadian zip codes which have an empty space in them. I have a few rows of data that also have leading blank spaces, but I can repair those in Access. Also, if it's easier or better to break this column of concatenated fields up in Access rather than Excel, that's not a problem.

I appreciate any help!