Extract Mailing Address to multiple cells

[Eric]

I have been provided with a few thousand mailing addresses. Each address is
written into a single cell, i.e. 123 Fair St. Orlando FL 12345, instead of
123 Fair St. / Orlando / FL / 12345.

I need to break out the addresses into separate columns by Street / City /
State / Zip.

I am familiar with the text to columns function, but the addresses are not
consistent enough to make this straight forward.

Any help is greatly appreciated.

Thank You,

 

[Ron Rosenfeld]

I have been provided with a few thousand mailing addresses. Each address is
written into a single cell, i.e. 123 Fair St. Orlando FL 12345, instead of
123 Fair St. / Orlando / FL / 12345.

I need to break out the addresses into separate columns by Street / City /
State / Zip.

I am familiar with the text to columns function, but the addresses are not
consistent enough to make this straight forward.

Any help is greatly appreciated.

Thank You,

It can be done using formulas, but the difficult part will be the break between
the street address and the city. It would be simple if all the cities in your
list are a single word. But that would be unusual.

Do you have a list of city names we could use?

Here is the general algorithm:

Zip code: last "word" in the string, all digits with an optional "-"
State: 2 letter word preceding zip code

Street address beginning of string to start of City
City: look up from list

--ron