Canadian Postal Code format?

[Fritz]

I would like to type is our Canadian Postal Code and have a special format
that forces the right entry. Our Postal Code consists of
3 characcters a space and 3 more character. Ie "S0L 1G9".
It is always a "UPPERCASE LETTER, number, UPPERCASE LETTER, space, number,
UPPERCASE LETTER, number".

I would like to enter it in lower case but have it converted to upper case.
Is this possible. Thanks

 

[Dave Thomas]

Use the UPPER function: if "a1b 6c3" (no quotes, of course) is entered in
A1, then in B1, =UPPER(A1) produces A1B 6C3

Regards,

Dave

 

[T. Valko]

I'm not the best VBA programmer but this works ok for me.

You could use an event macro.

Assume the range of interest is A1:A100 (adjust to suit)

Select the sheet where you want this to happen.
Right click the sheet tab and select View Code.
Paste this code into the window that opens.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ErrHandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
Target.Value = UCase(Target.Value)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

Try it out.

 

[Fritz]

Thanks that works fine as long as it take 2 cells to do it in. In Access we
used to be able to design a "special format" for such.

 

[Dave Thomas]

Excel is a number cruncher. Data validation in Excel is, for all practical
purposes, almost non-existent unless you use VBA code. Even the data
validation on the Data Menu which allows some validation is weak as you can
paste bad data into a cell even though the cell has data validation because
the data validation is ignored on a paste. Go figure! So, if you really need
to validate your data, learn Visual Basic for Applications.

Regards,

Dave

..

 

[T. Valko]

I didn't think they wanted "validated" data. I thought they just wanted
something to automatically convert the entry to uppercase. If they do want
"validated" data then they'll have to wait for a good programmer to stop by!

 

[Dave Thomas]

Would a plan of attack be to enter the data in Access, do the data
validation, and then import the data into Excel?

 

[Toppers]

Includes validation:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim postcode As String
Dim i As Integer
On Error GoTo ErrHandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
postcode = UCase(Target.Value)
End If
If Len(postcode) = 6 Then
postcode = Left(postcode, 3) & " " & Right(postcode, 3)
Target.Value = postcode
End If
For i = 1 To 7
Select Case i
Case Is = 1, 3, 6
If Not Mid(postcode, i, 1) Like "[A-Z]" Then
MsgBox "Error in post code " & postcode
Exit For
End If
Case Is = 2, 5, 7
If Not Mid(postcode, i, 1) Like "[0-9]" Then
MsgBox "Error in post code " & postcode
Exit For
End If
Case Else
If Mid(postcode, i, 1) <> " " Then
MsgBox "Error in post code " & postcode
Exit For
End If
End Select
Next i
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Teethless mama]

Why make thing so complicated. Keep it simple, all you have to do is press
the "cap lock" button and type your postal code

 

[Gord Dibben]

Fritz

There is no Custom Format for Canadian Postal codes that I have found.

You can use event code in the worksheet so that as you enter the code, it will
change to A1A 1A1

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 1 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
With Target
.Formula = UCase(Target.Formula)
.Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3))
End With
ErrHandler:
Application.EnableEvents = True
End Sub

As written this event code operates on Column A only.

You can type the code in as upper or lower case. Will come out as upper case no
matter what.

Right-click on the sheet tab and "View Code"

Copy/paste the above into that sheet module.

Adjust for your column if needed.

i.e. for just column B edit to If Target.Column <> 2 Then Exit Sub


Gord Dibben MS Excel MVP..................and Canuck