Hi, I'm hoping to find a simple way to clean up the following. I have some address data that needs to be cleaned up. Here's a sample:
City State ZIP
Plymouth IN 46563
Comstock Park MI 49321
Flint Mi 48532
Owosso Mi 48423
I want to separate out the city, state, and ZIP into separate columns, like in the first row. I want to first move the 5-digit ZIP to the ZIP column, and then move the two-letter state abbreviation, changing both characters to uppercase, to the State column. Seems to me that it should be a fairly simple fix, but I'm really stumped on how to proceed.
Solved! Go to Solution.
Please use the below in Regex tool
Regular expression:
(.+)\s+([a-z]{2})\s+(\d{5})
Output Method: Parse
Also post which add a formula tool to convert to Upper case.
Uppercase([Sate])
Many thanks
Shanker V
That seems to work perfectly. Is there a way to get it to dump the parsed information into the already-existing columns? If I specify the City, State, and ZIP columns that already exist, the REGEX tool creates City1, State1, and ZIP1, which is not optimal.
To do that you would modify the regex to work in a formula tool. To get the City for instance you could do Regex_Match([DataField],'(.+)\s+([a-z]{2})\s+(\d{5})','$1')
Thank you both!