Canadian Postal Code Validation?

**Ulchie** · 01-10-2020, 12:55 PM

I searched some other threads and saw some things but none appeared to match perfectly?

All I need is a formula to tell me TRUE or FALSE. False alerting me when a character or number is in wrong spot, or we have an invalid character or too many characters.

I am okay with a mix of upper and lower case letters.
I am okay if they don't have the space in the middle. (standard Canadian Postal code format is "A1A 1A1") (Letter,number,letter,SPACE,number,letter,number). But I'm okay if it doesn't have a space.
So I have some entries that have 6 digits and some have 7 (if they have space or not.)
I do not want any special characters (dashes, comma's, etc)

How do we make something to just tell me TRUE or FALSE - (False being when they either have a weird special character or too many or too few characters, or a character or number in the wrong spot)? I do not need to change them to correct them at this time.

**AliGW** · 01-10-2020, 12:57 PM

Welcome to the forum.

A small sample workbook with a few lines of representative postal codes showing the issues would help. See instructions at the top of the page.

**Ulchie** · 01-10-2020, 01:09 PM

Hi AliGW,

I scrubbed columns and only show the postal codes.
I am trying to get this data in Salesforce and it is okay if there are no spaces in the postal code, and it's okay if they are upper or lower case letters.

Postal code should be either:
Letter,number,letter,SPACE,number,letter,number
OR
Letter,number,letter,number,letter,number.

Some of those have the letter or number in wrong spot.
Some have comma's at the end.
Some have too many spaces.
Some have dashes or other symbols.

All I want to do is have it say TRUE or FALSE. False being when it has an incorrect character, character/number in wrong spot, or too many or too few characters.

**AliGW** · 01-10-2020, 01:16 PM

OK - can you please provide a small sample with the expected result (TRUE or FALSE) entered manually. 10-15 rows showing a selection will be fine.

**Ulchie** · 01-10-2020, 01:32 PM

Sure! I added different examples with TRUE and FALSE.

**AliGW** · 01-10-2020, 01:36 PM

One question - can the number (either side of the space) have two digits?

**Ulchie** · 01-10-2020, 01:38 PM

Always just 1 digit. Postal Codes are "A1A 1A1" or "a1a 1a1" (with or without the space). ie, "letter,number,letter,number,letter,number"

**AliGW** · 01-10-2020, 01:45 PM

Do you mind helper columns? Of course, they can be hidden.

**shg** · 01-10-2020, 01:46 PM

I believe also letters D, F, I, O, Q and U are not used, and the first position does not use letters W or Z.

So after upper-casing and removing spaces, like

"[ABCEGHJKLMNPRSTVXY]#[ABCEGHJKLMNPRSTVWXYZ]#[ABCEGHJKLMNPRSTVWXYZ]#"

**AliGW** · 01-10-2020, 01:48 PM

Maybe this (long-winded, but it seems to work):

Excel 2016 (Windows) 32 bit

	A	B	D	E	F	G	H	I	J	K
1	postal_code		HELPER COLUMNS
2	T3A 6E1	TRUE	T3A6E1	6	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
3	VOj 1eo	FALSE	VOj1eo	6	TRUE	FALSE	TRUE	TRUE	TRUE	FALSE
4	V0J1X0	TRUE	V0J1X0	6	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
5	V0J1E1	TRUE	V0J1E1	6	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
6	V0v 1c0	TRUE	V0v1c0	6	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
7	V0V-1B	FALSE	V0V-1B	6	TRUE	TRUE	TRUE	FALSE	TRUE	FALSE
8	V8G-1K8	FALSE	V8G-1K8	7	TRUE	TRUE	TRUE	FALSE	TRUE	FALSE
9	T3a6e1	TRUE	T3a6e1	6	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
10	T3A6EI	FALSE	T3A6EI	6	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE
11	T3A	FALSE	T3A	3	TRUE	TRUE	TRUE	FALSE	TRUE	FALSE
12	T3A 6E11	FALSE	T3A6E11	7	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
13	T3A6E11	FALSE	T3A6E11	7	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
14	T3A6E1,	FALSE	T3A6E1,	7	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
15	T3A 6E1	TRUE	T3A6E1	6	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
16	t3A6e1	TRUE	t3A6e1	6	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
17	t3A 6E1	TRUE	t3A6E1	6	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE

Sheet: data-validation-needed2 AliGW

Excel 2016 (Windows) 32 bit

	B	C	D	E	F	G	H	I	J	K
2	=IF(AND(E2=6,F2,G2,H2,I2,J2,K2),TRUE,FALSE)		=SUBSTITUTE(A2," ","")	=LEN(D2)	=ISTEXT(LEFT(D2,1))	=ISNUMBER(--MID(D2,2,1))	=ISTEXT(MID(D2,3,1))	=ISNUMBER(--MID(D2,4,1))	=ISTEXT(MID(D2,5,1))	=ISNUMBER(--MID(D2,6,1))

Sheet: data-validation-needed2 AliGW

**Ulchie** · 01-10-2020, 01:50 PM

Helper columns are fine.

At end of day, I found out I had a years worth of data integration issues because I was using a 3rd party (Zapier) to transfer leads from my landing page software to Salesforce. I didn't realize why I was getting discrepencies until I implemented a direct integration recently and Salesforce actually gave me errors to do mostly with postal codes. So I looked and anytime someone typed incorrect postal codes in past, it didn't go into salesforce. I have fixed this moving forward so people ahve to type a valid postal code, but I have thousands of leads going back that I want to check and see which ones likely didn't go into salesforce because of this postal code issue.

Thanks for helping me!!!

**AliGW** · 01-10-2020, 01:52 PM

I've outlined a possible solution above. Attached here.

**Ulchie** · 01-10-2020, 02:00 PM

Sorry, I just saw the whole post above my previous that has your stuff..

Line 12,13,14 doesn't make sense to me... It should be false, but you have true all the way through? or will column B still work properly I assume? Your attachment didn't show the formulas for some reason but I see them up above in your post.

**Ulchie** · 01-10-2020, 02:06 PM

Well it looks like it works when I plug it in. So happy. Thank you a ton. I'll have to chekc with more examples but I think I got it! You rock.!!!!!!!

**AliGW** · 01-10-2020, 02:06 PM

No - 12, 13 and 14 are FALSE (see column B, where the cumulative result is).

Attaching again.

**AliGW** · 01-10-2020, 02:07 PM

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

**Ulchie** · 01-10-2020, 02:07 PM

Thanks! You're amazing.

Canadian Postal Code Validation?

LinkBack

Thread Tools

Rate This Thread

Display

Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Re: Canadian Postal Code Validation?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Issue with Validating a Canadian Postal Code in excel 2016

Format Canadian Postal Code on Entry

Canadian postal code format

Canadian Postal Code format

[SOLVED] Userform for canadian postal code validation?

[SOLVED] Help With Canadian Postal Code In Userform

Adding spaces to Canadian postal code

Bookmarks

Bookmarks

Posting Permissions