Cdn Postal Code

[Christine]

How do I creat an input mask for the the Canadian postal code?

I would really like the person the be able to type v2v2v2 and have it
formatted V2V 2V2. I can do this in Access so I was hoping I could do it in
Excel. It's either an input mask or formatting - I really don't know the
proper terminology.

Normally, I would use Access for this particular task but the person I'm
creating this for doesn't have the software.

Sorry if this is a repeated question but I just don't use Excel too often.

Thank you!

 

[Bernie Deitrick]

Christine,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

Change the
Set rngPCode = Range("D")
to the column where the postal code will be entered.

This event will allow either 6 character entry or 7 character entry (with the 4th being a space) in
the column.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngPCode As Range
Set rngPCode = Range("D")
If Intersect(rngPCode, Target) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo Reset
Application.EnableEvents = False
If (Mid(Target.Value, 4, 1) = " ") And (Len(Target.Value) = 7) Then
Target.Value = UCase(Target.Value)
Else
If Len(Target.Value) = 6 Then
Target.Value = Left(UCase(Target.Value), 3) & " " & Right(UCase(Target.Value), 3)
Else
MsgBox "You need to enter a valid Postal Code"
Application.Undo
End If
End If
Reset:
Application.EnableEvents = True

End Sub