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!

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

"Christine" wrote in message
...
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!

Excel lets you make formats for numbers but not masks for text
Only thing I can suggest is using formula =UPPER(LEFT(A1,3)&" "&RIGHT(A1,3))
to do the conversion
Later you could use Copy followed by Paste Special Values to convert that
column to text. This allows you to delete the input column
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Christine" wrote in message
...
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!

Thank you both!

"Bernard Liengme" wrote:

Excel lets you make formats for numbers but not masks for text
Only thing I can suggest is using formula =UPPER(LEFT(A1,3)&" "&RIGHT(A1,3))
to do the conversion
Later you could use Copy followed by Paste Special Values to convert that
column to text. This allows you to delete the input column
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Christine" wrote in message
...
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!