Validate and Clean Canadian Postal Codes in Microsoft Excel

If you want to cleanup and validate a list of Canadian postal codes in Microsoft Excel, you’ve come to the right place! With the formulas below, you can ensure postal codes are properly formatted and conform to the standard postal code format.

Step 1: Clean the Data

The formula below will remove any excess white space, and ensure that the postal code is formatted with uppercase letters and a single space in the middle. If this step is not done, the validation formula in step 2 may falsely report a postal code as invalid when it is simply improperly formatted.

=LEFT(TRIM(SUBSTITUTE(UPPER(A1), " ", "")),3)&" "&RIGHT(TRIM(SUBSTITUTE(UPPER(A1), " ", "")),3)

Step 2: Validate the Postal Code

The formula below will return TRUE/FALSE depending on whether the postal code is found to be valid. The formula ensures that the postal code has letters and numbers in the correct places, and is exactly 7 characters in length (including the expected space in the middle). It also ensures that the postal code does not contain letters that are not found in valid postal codes. This is done using the EXACT function to find whether the letter appears in a list of accepted letters. Since we pass an array to EXACT, we will receive an array back. The MATCH function is used to determine if there was a match with any of the letters in our list. If the letter wasn’t found in the list, EXACT will return an array of FALSE and MATCH will return an error. Since the error value is not a number, our formula will return false in that case.

# Easy to read format
=AND(
    LEN(B1)=7,
    ISNUMBER(VALUE(MID(B1,2,1))),
    ISNUMBER(VALUE(MID(B1,5,1))),
    ISNUMBER(VALUE(MID(B1,7,1))),
    ISNUMBER(MATCH(TRUE, EXACT(MID(B1, 1, 1), {"A","B","C","E","G","H","J","K","L","M","N","P","R","S","T","V","X","Y"}), 0)),
    ISNUMBER(MATCH(TRUE, EXACT(MID(B1, 3, 1), {"A","B","C","E","G","H","J","K","L","M","N","P","R","S","T","V","W","X","Y","Z"}), 0)),
    ISNUMBER(MATCH(TRUE, EXACT(MID(B1, 6, 1), {"A","B","C","E","G","H","J","K","L","M","N","P","R","S","T","V","W","X","Y","Z"}), 0)),
    MID(B1,4,1)=" "
)

# Same as above but easier to copy into Excel
=AND(LEN(B1)=7,ISNUMBER(VALUE(MID(B1,2,1))),ISNUMBER(VALUE(MID(B1,5,1))),ISNUMBER(VALUE(MID(B1,7,1))),ISNUMBER(MATCH(TRUE, EXACT(MID(B1, 1, 1), {"A","B","C","E","G","H","J","K","L","M","N","P","R","S","T","V","X","Y"}), 0)),ISNUMBER(MATCH(TRUE, EXACT(MID(B1, 3, 1), {"A","B","C","E","G","H","J","K","L","M","N","P","R","S","T","V","W","X","Y","Z"}), 0)),ISNUMBER(MATCH(TRUE, EXACT(MID(B1, 6, 1), {"A","B","C","E","G","H","J","K","L","M","N","P","R","S","T","V","W","X","Y","Z"}), 0)),MID(B1,4,1)=" ")