Canadian Postal Code format?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fritz - 23 Jul 2007 03:46 GMT

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

Reply to this Message

Dave Thomas - 23 Jul 2007 04:18 GMT

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

>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
[quoted text clipped - 5 lines]
> case.
> Is this possible. Thanks

Reply to this Message

Fritz - 23 Jul 2007 04:46 GMT

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.

> Use the UPPER function: if "a1b 6c3" (no quotes, of course) is entered in
> A1, then in B1, =UPPER(A1) produces A1B 6C3
[quoted text clipped - 12 lines]
> > case.
> > Is this possible. Thanks

Reply to this Message

Dave Thomas - 23 Jul 2007 05:22 GMT

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

.

> Thanks that works fine as long as it take 2 cells to do it in. In Access
> we
[quoted text clipped - 19 lines]
>> > case.
>> > Is this possible. Thanks

Reply to this Message

T. Valko - 23 Jul 2007 05:39 GMT

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!

Signature

Biff
Microsoft Excel MVP

> 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
[quoted text clipped - 32 lines]
>>> > case.
>>> > Is this possible. Thanks

Reply to this Message

Dave Thomas - 23 Jul 2007 05:41 GMT

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

> Thanks that works fine as long as it take 2 cells to do it in. In Access
> we
[quoted text clipped - 19 lines]
>> > case.
>> > Is this possible. Thanks

Reply to this Message

Gord Dibben - 23 Jul 2007 21:57 GMT

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

>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.
[quoted text clipped - 15 lines]
>> > case.
>> > Is this possible. Thanks

Reply to this Message

T. Valko - 23 Jul 2007 04:36 GMT

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.

Signature

Biff
Microsoft Excel MVP

Reply to this Message

Toppers - 23 Jul 2007 08:36 GMT

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

> I'm not the best VBA programmer but this works ok for me.
>
[quoted text clipped - 29 lines]
> > case.
> > Is this possible. Thanks

Reply to this Message

Teethless mama - 23 Jul 2007 15:22 GMT

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

> 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
[quoted text clipped - 4 lines]
> I would like to enter it in lower case but have it converted to upper case.
> Is this possible. Thanks

Reply to this Message