Splitting Data into Columns

[ordomar]

How do I split a column into data with zip codes from different countries? 

[DataNath]

@ordomar can you copy a list of some ZIP codes here and what the output should look like? Will help when building the solution/RegEx formula.

[ordomar]

11414Howard Beach, NY
58078West Fargo, ND
31204Macon, GA
38671Southaven, MS
44256Medina, OH
42420Henderson, KY

[ordomar]

17701Williamsport, PA
32707Casselberry, FL
30188Woodstock, GA
P0P 7S7Manitoulin, ON
B0T 8X8Queens County, NS
H5A 6K7Place Bonaventure, QC
L4K 4J0Concord, ON
K1B 1G8Gloucester, ON
M8V 6B7Etobicoke, ON
V2J 8V4Quesnel, BC
B1B 1Y0Port Morien, NS
L9S 7Y1Innisfil, ON

[DataNath · Accepted solution]

If you put the following into your RegEx parse expression, does that split how you want?

([0-9A-Z\s]+)([A-Z]{1}[A-Za-z\s]+),\s(\w+)

[ordomar]

I am trying to get the first five digits of the US zip code in a column along with the canadian zicodes which has 6 digits: 

Before and after: 

30188Woodstock, GA   needs to 30188   Woodstock   GA  
P0P 7S7Manitoulin, ON              P0P 7S7  Manitoulin   ON

[ordomar]

I need 3 columns: zipcode, city, and region

[ordomar · Accepted solution]

I apologize, the regex did work, I did not deselect case sensitive. Thank you! 

[DataNath]

No problem @ordomar - happy to help! Though I think you marked the wrong post as the solution - if you mark the one containing the RegEx expression that may help others to find what they're looking for if they come across this! 😀

[lynnekilgore]

I just used this for my own purposes and it worked well. Thank you!