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