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

[nberumen]

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 0B8

Toronto ON N6D 2C6

West Kelowna BC U2Y 4K5

Vancouver B.C. U7C 6B2

Dorval QC J8L3Z9

Truro NS C3M 7U3

Desired Outcome:

Billing City / District	Billing Zip Code
land Quebec	H8L 0B8
Toronto ON	N6D 2C6
West Kelowna BC	U2Y 4K5
Vancouver B.C.	U7C 6B2
Dorval QC	J8L3Z9
Truro NS	C3M 7U3

[christina-billman · Accepted solution]

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:

1. Import the dataset into Alteryx
2. Use a "find and replace" tool (found in the join tab) to join this new zipcode file to your original data file.
3. 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
4. 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 solution]

@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})$

[nberumen]

Thank you christina-billman and DataNath!

These both worked great and helped me out a bunch, I appreciate the help!

Thanks again,

nberumen