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.

"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.

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.

Hi Gord,

I think your clock is several hours fast.

Pete

On Jul 13, 5:45 am, Gord Dibben <gorddibbATshawDOTca wrote:
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.- Hide quoted text -

- Show quoted text -

Try this:

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


"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.

Thanks Pete.

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


Gord

On Thu, 12 Jul 2007 14:41:46 -0700, Pete_UK wrote:

Hi Gord,

I think your clock is several hours fast.

Pete

On Jul 13, 5:45 am, Gord Dibben <gorddibbATshawDOTca wrote:
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.- Hide quoted text -

- Show quoted text -

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.

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.

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.