Help on extract data from an address field into separate fields

[DrWill]

Hi All,

I have a text field which is used for a full property address. I need to extract this data into separate fields, [street address], [city], [state] and [zip]. There are variables I don't know how to address when using Leftwords, Middlewords and Rightwords. For example, an address maybe easy, such as [123 Main Street] [Manhattan], [KS] [88888], while another may be [1234 East 164th Ave, Ste 100] [Saint Louis], [MO] [88888]. Wondering is anyone knows a way to make work or can give advice.

Thanks for any help!

[comment]

I am assuming those brackets are not present in the original data? Unless you can find some rules that even someone who doesn't understand the meaning of the address (such as a computer) could use, the task will be impossible to automate.

Now, the two given examples suggest that it would be quite easy to extract the state and the ZIP code; in fact, you have several choices here, e.g. the last two words, or the last 2 + 5 characters, or the part after the last comma. However, I do not see an equally easy way to separate the street address part from the city name.

 

Edited April 29, 2022 by comment