Parse Address, City, State, Zip from data

Question

I am trying to parse out the address data into Address, City, State, Zip columns. I have been trying to get RegEx to work but i am confuse how to do this when there not in any uniform order. Is there a simpler way then RegEx, if not how would this be done in RegEx.

Wayne Vebeen Michigan United Stateskathy Charry One Melvin Avenue Annapolis, Maryland 21401 United StatesCornerstone Architects 7000 Bee Caves Rd. Suite 200 Austin, Texas 78746 United Statesiugene sakai 1000 South Winchester Blvd. San Jose, California 95128 United StatesJeff Pelletier 115 15th Ave E. Suite 100 Seattle, Washington 98112 United StatesJames Krisp 16 Washington Avenue Millbrook, New York 12545 United States300 S. Orange Avenue Sarasota, Florida 34236 United StatesNashville, Tennessee 37210 United States2429 Medinah Dr. Evergreen, Colorado 80439 United States111 W El Prado Dr San Antonio, Texas 78212 United States

cgoodman · Accepted Answer

Edit: I just realised you also want to get the City and the above solution leaves the city in with the address field. The tricky bit here is you have some cities which are two words. Can the data be exported in a way where you add an additional delimiter?

Otherwise my other solution would be to use lists of US cities and use the find and replace tool to search the string and append the city if there is a match. See attached.

Another option is the CASS address parsing tool, but I don't have this dataset but someone else who does can comment on whether this is also an option.