Start a new topic

Regular Expression: Canadian Province from Postcode

I recently created a regular expression dictionary for someone who wanted to assign the Canadian province abbreviation based on just the first letter of the postcode. In this example, you will see that any postcode starting with the letter "A" or "a" will return the value of "NL" (Newfoundland). Please note that this formula works best when there are no spaces in the string for Value to Match on, which I handled in the example below by first replacing the space [ ] with a value of -BLANK-. This dictionary is linked as an excel document below so you can copy/paste into a new blank dictionary to try it out for yourself!

Canadian Province from Postcode.xlsx

Here are the formulas I used to assign NL (Newfoundland) for a Postcode beginning with "A" or "a":

Replacement Value: NL

Value to Match On: ^([Aa]\w+)\S{3}$

image

To make this useful, you will need to copy the field ('Copy Field' function) containing Postcode (that would be your 'seed') and map as Province in Virtual Fields with this dictionary applied.

A great Regular Expression resource site that can teach you how to craft your own regular expressions such as this is: http://regexone.com/

Keep up the good work!

Allison B.

Login or Signup to post a comment