What regex formula can parse out Canadian Zip Codes from an addresses?

Question

Hello Everyone🖐!

I'm still super new to regex and need help with parsing out these Canadian Zip Codes from these addresses, what is the best formula for this and can you show what each expression means please?

Billing City / District

land Quebec H8L 0B8Toronto ON N6D 2C6West Kelowna BC U2Y 4K5Vancouver B.C. U7C 6B2Dorval QC J8L3Z9Truro NS C3M 7U3

Desired Outcome:

Billing City / District

Billing Zip Code land QuebecH8L 0B8Toronto ONN6D 2C6West Kelowna BCU2Y 4K5Vancouver B.C.U7C 6B2Dorval QCJ8L3Z9Truro NSC3M 7U3

christina-billman · Accepted Answer

Unfortunately with Regex, a field needs to have a consistent pattern to match on, and I'm not seeing that in the data you have here. What I can recommend, however, is using a free public data set of all of the cities or zip codes in Canada, and matching on that.

Here's a google search to get you started, looks like there's lots of options.

Once you have the dataset, I would suggest the following:

* Import the dataset into Alteryx
* Use a "find and replace" tool (found in the join tab) to join this new zipcode file to your original data file.
* Select "any part of field". This will search for the zip code (from the new file) and try to find it in your original document anywhere in your [Billing City / District] column once you have it configured
* Then at the lower part of the configuration, select Append Fields to Record and check the zip code from the zipcode file.

Let me know if this did the trick!

DataNath · Accepted Answer

@nberumen do you have any luck with the following? I’m not too familiar with Canadian zip codes but from a quick Google search it seems they should always be 6 chars with an optional space?

RegEx tool, in parse mode, using expression:

\s([\w\s]{6,7})$