Delimiting Excel Addresses

Copper Contributor

I have a list of 19,000+ addresses all in one cell. I need the information delimited into the following columns: Address line 1, Address Line 2, City, State, Zip Code. Unfortunately, the format of the addresses vary from line to line. Some use 5-digit zip codes, others 9-digit. Some have been separated by commas, other no separation. Some also do not have the need for Address Line 2 (i.e. Suite 100). This make the standard "Text to Columns" features void of power. What other address recognition in excel could be used to delimit the data?

1 Reply
It seems you may initially employ Text to Columns with space as delimiter, whereafter you decipher your next action.