Split column based on REGEX pattern

[SergeantJack]

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.

[ShankerV · Accepted solution]

Hi @SergeantJack 

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

[SergeantJack]

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.

[Luke_C · Accepted solution]

Hi @SergeantJack 

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')

[SergeantJack]

Thank you both!