formula for Canadian postal codes

[LB]

Hi there. I'd like to type postal codes this way:
b3j2m7 and have a formula that would change it to:
B3J 2M7

What options do I have? Thanks.

 

[Harlan Grove]

Hi there. I'd like to type postal codes this way:
b3j2m7 and have a formula that would change it to:
B3J 2M7

What options do I have? Thanks.

1. Type it this way (in cell x), but display it in a different cell
using the formula =REPLACE(x,4,0," ").

2. Use a Change or SheetChange event handler (a type of VBA macro
triggered by any cell entry) to insert spaces.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
On Error Resume Next
If Intersect(Target, _
Me.Names("CPCs").RefersToRange) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In Target
If Not c.HasFormula And Not IsEmpty(c.Value) Then c.Value = _
Left(LTrim(c.Value), 3) & " " & Right(RTrim(c.Value), 3)
Next c
Application.EnableEvents = True
End Sub

Note that this relies on a worksheet-level defined name, CPCs, which
refers just to the range that should contain the postal codes.

 

[Pete_UK]

Hi Gord,

I think your clock is several hours fast.

Pete

 

[Guest]

Try this:

=REPLACE(UPPER(A1),4,0," ")

 

[Gord Dibben]

Thanks Pete.

See answer to Roger Govier in "inserting static time" post.


Gord

 

[Gord Dibben]

For postal codes in existing cells..................

In an adjacent column............say H assuming codes are in G

=UPPER(LEFT(G1,3)&" "&RIGHT(G1,3))

Double-click on the fill handle of H1 to fill down.

For new entries you could use event code to change them as you entered them.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 7 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

This is event code. Select the sheet tab and "View Code".

Copy/paste the above into that module.

As written operates only on column G........Target.Column <> 7

Existing entries in Column G could also be changed by selecting each cell and F2
then ENTER


Gord Dibben MS Excel MVP

 

[LB]

Hi there. thanks for the information - it worked like a charm. Someone
else here is using Excel 2007. I tried using the event code but that didn't
work in 07 - is there something different for that version?

Thanks.
KL

 

[Gord Dibben]

I do not have 2007 installed so can't speak to that.

I would certainly hope that event code would operate the same in 2007.

There must be something other than version that would prevent the code from
working.

A 2007 user will jump in and let us know.


Gord