canadian postal codes

[dr]

Postal codes in Canada are A1A 1A1. I sent an Excel file
to an address verification program and the postal codes
come back A1A1A1 without the space. Canada Post cannot
read the postal codes without the space. How can I tell
Excel 2000 to put a space in?
The verification program cannot change how the postal
codes come back. Moving to Access is not an option. I
tried to make a Custom number format, but not didn't have
much success. Also tried a Macro but that didn't work
either.
Are there any templates out there or any other ideas.
thanks

 

[Don Guillett]

I know nothing about these postal codes but if always 3 letters & space & 3
letters, this will work
Sub makespace3()
For Each c In Selection
c.Value = Left(c, 3) & " " & Right(c, 3)
Next
End Sub

 

[Jason Morin]

One way:

=REPLACE(A1,4,," ")

HTH
Jason
Atlanta, GA

 

[Andy B]

Hi

You could use a helper column with
=LEFT(A1,3)&" "&RIGHT(A1,3)
This would recreate the information with a space. AutoFill this down the
column as required. Once you are happy with this, select the whole range and
Edit / Copy and then Edit / Paste Special / Values. You can then delete the
old range of data

 

[Gord Dibben]

dr

You have acouple of manual fixes.

If you need macro..........

Sub Add_Space()
Dim cell As Range
Dim thisrng As Range
On Error GoTo endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
For Each cell In thisrng
cell.Value = Left(cell.Value, 3) & " " & Right(cell.Value, 3)
Next
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub

Gord Dibben Excel MVP