Seperating Different Address Formats in Excel

[DodgerTX]

Hello. I have gotten a data dump with addresses. I need to seperate them out into a mailing address. (i.e. column for address, Suite # if needed, city, state, zip) , however the information is in differerent formats. Some use commas, some do not. Some list 9 digit Zip, others do not. Some have US at the end, others do not, etc. Ive tried delimited, and that works some, but clean up still requires a lot of work. Original data is almost 100k lines, so not something I want to do on a regular basis. Any ideas would be appreciated. Thanks.

 

[kweaver]

Without a consistent pattern, this is fairly complicated. Since you have 365 have you tried flash fill? Might get many of them.