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


On Tue, 17 Jul 2007 16:25:15 -0300, "LB" wrote:

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



On Thu, 12 Jul 2007 16:09:05 -0300, "LB" wrote:

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.