Replace/Format?

[Ivor Williams]

I have a worksheet with a column containing Canadian Postal Codes. The
proper format for the postal code is A1A 1A1. The codes in my worksheet are
proper except there is no space between the two groups of three characters.
How can I insert the space in every row of the column in one move?

Ivor

 

[Dave R.]

One move?

It looks like you want to edit the contents of a cell (and not the format
since you say you want to insert a space) without moving data around to/from
any other cells, and I for one don't think Excel has any one-move things
that can do that in this case. Search and replace would work if you always
had the same string you wanted to insert a space into, but you don't I
assume.
I would use a helper column to the right, with a formula taking the first
left 3 characters, a " ", then the right 3 characters. then paste special
those over the originals.

 

[Mark Graesser]

Ivor
In a scrap column enter the formula

=LEFT(A1,3)&" "&RIGHT(A1,3

changing the A1 to the appropriate cell reference. Copy this down to the bottom of your list. Then select the scrap column, Copy, and Paste_Special>Values over the top of you original data

Good Luck
Mark Graesse
(e-mail address removed)

----- Ivor Williams wrote: ----

I have a worksheet with a column containing Canadian Postal Codes. Th
proper format for the postal code is A1A 1A1. The codes in my worksheet ar
proper except there is no space between the two groups of three characters
How can I insert the space in every row of the column in one move

Ivo

 

[Alan]

I don't think it can be done in one move though I may be wrong,
=LEFT(A1,3)&" "&RIGHT(A1,3)
in another column will give what you want, you could the Copy > Paste
Special Values over the originals,
Regards,
Alan.

 

[Gord Dibben]

Ivor

Either a helper column with a formula.....

=LEFT(A1,3)&" " & RIGHT(A1,3) which would be copied down the helper column.

When happy with results, copy>paste special(in place)>values. Delete original
column.

Or a macro to add the space in the selected range.

Sub Add_Text_Left()
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 XL2002