Format Canadian Postal Code on Entry

**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

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range
   
   For Each Rng In Target
      If Rng.Column = 1 Then
         Application.EnableEvents = False
         Select Case Len(Rng.Value)
         Case 6
            Rng.Value = UCase(Left(Rng.Value, 3) & " " & Right(Rng.Value, 3))
         Case 7
            Rng.Value = UCase(Rng.Value)
         End Select
      End If
   Next Rng
   Application.EnableEvents = True
End Sub

**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

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range
   Dim iChar As Integer
   
   For Each Rng In Target
      If Rng.Column = 1 Then
         Application.EnableEvents = False
         Select Case Len(Rng.Value)
         Case 6
            Rng.Value = UCase(Left(Rng.Value, 3) & " " & Right(Rng.Value, 3))
         Case 7
            Rng.Value = UCase(Rng.Value)
         End Select
         If Len(Rng.Value) <> 7 Then
            MsgBox "Invalid Post Code Entyry in " & Rng.Address
            Exit For
         End If
         For iChar = 1 To 7 Step 1
            Select Case Mid(Rng.Value, iChar, 1)
            Case "A" To "Z"
               Select Case iChar
               Case 2, 4, 5, 7
                  MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                  & " is invalid for Postcode"
                  'rng.ClearContents
                  Exit For
               End Select
            Case 0 To 9
               Select Case Mid(Rng.Value, iChar, 1)
               Case 1, 3, 4, 6
                  MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                  & " is invalid for Postcode"
                  'rng.ClearContents
                  Exit For
               MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                  & " is invalid for Postcode"
                  'rng.ClearContents
                  Exit For
               End Select
            Case " "
               Select Case Mid(Rng.Value, iChar, 1)
               Case 1 To 3, 5 To 7
                  MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                     & " is invalid for Postcode"
                  'rng.ClearContents
                  Exit For
               End Select
            Case Else
               MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                     & " is invalid for Postcode"
                  'rng.ClearContents
               Exit For
            End Select
         Next iChar
      End If
   Next Rng
   Application.EnableEvents = True
End Sub

**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

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range
   Dim iChar As Integer
   
   For Each Rng In Target
      If Rng.Column = 1 Then
         Application.EnableEvents = False
         Select Case Len(Rng.Value)
         Case 6
            Rng.Value = UCase(Left(Rng.Value, 3) & " " & Right(Rng.Value, 3))
         Case 7
            Rng.Value = UCase(Rng.Value)
         End Select
         If Len(Rng.Value) <> 7 Then
            MsgBox "Invalid Post Code Entyry in " & Rng.Address
            Exit For
         End If
         For iChar = 1 To 7 Step 1
            Select Case Mid(Rng.Value, iChar, 1)
            Case "A" To "Z"
               Select Case iChar
               Case 2, 4, 5, 7
                  MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                  & " is invalid for Postcode"
                  'rng.ClearContents
                  Exit For
               End Select
            Case 0 To 9
               Select Case iChar
               Case 1, 3, 4, 6
                  MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                  & " is invalid for Postcode"
                  'rng.ClearContents
                  Exit For
               End Select
            Case " "
               Select Case iChar
               Case 1 To 3, 5 To 7
                  MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                     & " is invalid for Postcode"
                  'rng.ClearContents
                  Exit For
               End Select
            Case Else
               MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                     & " is invalid for Postcode"
                  'rng.ClearContents
               Exit For
            End Select
         Next iChar
      End If
   Next Rng
   Application.EnableEvents = True
End Sub

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

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

Gos-C

**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

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range
   
   For Each Rng In Target
      If Rng.Column = 1 Then
         Application.EnableEvents = False
         Select Case Len(Rng.Value)
         Case 6
            Rng.Value = UCase(Left(Rng.Value, 3) & " " & Right(Rng.Value, 3))
         Case 7
            Rng.Value = UCase(Rng.Value)
         End Select
      End If
   Next Rng
   Application.EnableEvents = True
End Sub

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?