Format Canadian Postal Code on Entry

**Gos-C** · 02-05-2007, 09:11 PM

Hi everyone,

I can't find a custom format to validate a properly formatted Canadian postal code on entry, so I think my last hope is to ask someone to write me a VBA code.

Canadian postal codes consist of six characters with a space in the middle: a capital letter, a number, a capital letter, a space, a number, a capital letter, and a number. Therefore, M2A 3J4 is a properly formated postal code.

I want a code to fix an improperly fomatted postal code (such as M2A3J4 or m2a3j4 or m3a 2j4) on entry.

Can anyone please help me?

Thanks,
Gos-C

**mudraker** · 02-05-2007, 11:20 PM

Gos-C

Try this macro - It does not verify Letters & Number are placed in the correct order - This can be added if required.

Copy this macro
Goto Excel
Right Click on Sheet Name Tab > select View Code
Past macro into the Worksheet Module displayed

Change Column number to suit (shown In red in this posting)
Return to Excel and test

Please Login or Register  to view this content.

**Gos-C** · 02-06-2007, 09:10 AM

Hi Mudraker,

Thank you very much. It works but, if you don't mind, can you add the verification of the letters and numbers in the correct order. That would be perfect. I really appreciate your help.

Thanks again,
Gos-C

**mudraker** · 02-06-2007, 05:30 PM

Gos

This version verifies letters, numbers, space in correct location.

Change Column number to suit

For invalid Postcode entries if you remove the ' before 'rng.ClearContents it will clear the entry

Please Login or Register  to view this content.

**oldchippy** · 02-06-2007, 06:05 PM

Here's an alternative if you require a formula, with your data entry in A1, then this in B1

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

**Gos-C** · 02-07-2007, 10:34 AM

Hi Mudraker,

Thanks again for taking time to help me. I sincerely appreciate it.

When I enter an improperly formatted postal code -- e.g., a1b2c3 -- in cell A1, it gets formatted properly (A1B 2C3) but I get the message: $A$1 - 1 is invalid for Postcode. It happens all the time. Do I need to modify the code?

Thanks,
Gos-C

**mudraker** · 02-07-2007, 04:53 PM

I should have been a bit more thourgh on the testing before posting the code

Please Login or Register  to view this content.

**Gos-C** · 02-07-2007, 09:45 PM

Got it! Mudraker, you're the best! Thanks a million.

Gos-C

**mudraker** · 02-07-2007, 11:24 PM

Glad to hear all is now working correctly & thanks for the feed back

**gajendra.gupta** · 02-08-2007, 02:06 AM

I beleive this might also helpful if you are entering Postcode in Range 'A1'.

Sub Postal_Verify()
If Len(Range("A1").Text) = 6 And _
IsAlpha(Mid(Range("A1").Text, 1, 1)) = True And _
IsAlpha(Mid(Range("A1").Text, 3, 1)) = True And _
IsAlpha(Mid(Range("A1").Text, 5, 1)) = True And _
IsNumeric(Mid(Range("A1").Text, 2, 1)) = True And _
IsNumeric(Mid(Range("A1").Text, 4, 1)) = True And _
IsNumeric(Mid(Range("A1").Text, 6, 1)) = True Then
Range("A1") = Left(Range("A1"), 3) & " " & Right(Range("A1"), 3)
Else
MsgBox "Invalid Postal Code."
End If
End Sub
Function IsAlpha(chr As String) As Boolean
If Asc(chr) >= 97 And Asc(chr) <= 122 Or Asc(chr) >= 65 And Asc(chr) <= 90 Then
IsAlpha = True
Else
IsAlpha = False
End If
End Function

Regards,
Gajendra Gupta

**gajendra.gupta** · 02-08-2007, 02:41 AM

This is even more simpler..

Sub Postal_Verify()
If Range("D1") Like "[A-Za-z][0-9][A-Za-z][0-9][A-Za-z][0-9]" Then
Range("A1") = Left(Range("A1"), 3) & " " & Right(Range("A1"), 3)
Else
MsgBox "Invalid Postal Code."
End If
End Sub

Regards,
Gajendra Gupta

**mudraker** · 02-08-2007, 03:03 AM

Gajendra

Fantastic bit of code - I would never have thought of using Like

I have modified my macro to incorporate your code.

The new macro retains all the requirements as per Gos-C 1st posting and is much simpler than what I had coded

Please Login or Register  to view this content.

**Gos-C** · 02-09-2007, 12:11 PM

Hi Mudraker,

This is certainly going to be a popular/much sought-after code. Good job! I think it would be better if the invalid postal code is cleared when the message is shown. Do you mind adding this last bit?

Gajendra, thanks also for your great input.

Regards,
Gos-C

**mudraker** · 02-09-2007, 05:18 PM

Gos-C

Contents now cleared on invalid postcodes - New line shown in red

Please Login or Register  to view this content.

**Gos-C** · 02-09-2007, 06:43 PM

Perfect!

Thanks again, Mudraker.

Gos-C

**Zheng** · 04-12-2012, 01:00 PM

Hello,

I faces the same problem where i have a column of postal code and some of them are invalid. However, for some reason, I cannot run the macro. Nothing happened. I wonder why?

**Charles** · 04-12-2012, 01:29 PM

Zheng,

Welcome to the forum.
If you are asking a question then you should start your own thread.

**tbeshr** · 02-19-2013, 05:11 PM

Originally Posted by mudraker

Gos-C

Try this macro - It does not verify Letters & Number are placed in the correct order - This can be added if required.

Copy this macro
Goto Excel
Right Click on Sheet Name Tab > select View Code
Past macro into the Worksheet Module displayed

Change Column number to suit (shown In red in this posting)
Return to Excel and test

Please Login or Register  to view this content.

Hi Mudraker,

This work is fantastic and thank you in advance on behalf of all Canadian excel users who have been loosing sleep in trying to automate the post code entry/ format process!

I am a Excel 2011 for Mac user and when I right click on the Sheet Tab, I do not have an option to View Code. I tried pasting the code into the macro record window but now every entry in the sheet comes up with an 'Invalid Postal Code' error.

Could you please offer some (more!) guidance?

**cpost** · 03-08-2013, 03:06 PM

Does anyone know if I can use this code to force postal code entry in a user form text box?

**wils_venancio** · 06-03-2014, 04:46 PM

Hi, if I want to apply that validation to a specific cell, say cell C14, how would I modify the VBCode?

**Iridian** · 01-20-2015, 07:26 PM

Just applied this to a project - still working in 2015! Thanks!

**Gos-C** · 01-24-2015, 06:47 PM

Hi Iridian, I am happy you found it and were able to use it. Keep "excel-ling."

Gos-C

**ChrisFB** · 06-29-2015, 06:13 PM

Hi Gos-C,

I found the code and have inserted it (Excel 2010). The odd thing though is that the postal code is repeated five times. I hope the attached image is visible so you can see what I mean. Any ideas? Thanks for any help that you can provide!

Chris

PostalCode.JPG

**FDibbins** · 06-29-2015, 06:21 PM

Chris, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**ThorGuy** · 03-08-2017, 04:00 PM

Originally Posted by wils_venancio

Hi, if I want to apply that validation to a specific cell, say cell C14, how would I modify the VBCode?

Did you ever figure this out?

**onegr8lady** · 06-30-2018, 02:51 AM

Please delete

**AAC_Forms** · 03-24-2019, 04:20 PM

Hi Group,
New here today Best to all

Format Canadian Postal Code on Entry

LinkBack

Thread Tools

Rate This Thread

Display

Format Canadian Postal Code on Entry

Job Well Done

Re: Format Canadian Postal Code on Entry

Re: Format Canadian Postal Code on Entry

Re: Format Canadian Postal Code on Entry

Re: Format Canadian Postal Code on Entry

Re: Format Canadian Postal Code on Entry

Re: Format Canadian Postal Code on Entry

Re: Format Canadian Postal Code on Entry

Re: Format Canadian Postal Code on Entry

Re: Format Canadian Postal Code on Entry

Re: Format Canadian Postal Code on Entry

Re: Format Canadian Postal Code on Entry

Re: Format Canadian Postal Code on Entry

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions