macros

[Louise]

I have a worksheet with thousands of postcodes on.
However, the postcodes have been entered as one word, ie.
M445WE instead of M44 5WE. Is there a way I can ask
Excel to put a space after the first 3 characters?

Any urgent help would be appreciated.

Thank you.

Louise

 

[Andy B]

Hi

One problem with this is that not all UK postcodes are 3 characters then a
space and then 3 characters. One way to do what you ask, though, is with a
helper column with
=LEFT(A1,3)&" "&RIGHT(A1,3)
You can then autofill this down. If you happy with it then you should use
Edit / Copy on the rangeand then Edit / Paste Special / Values to fix the
values in place. It is then safe to delete the original data.

Hope this helps.

 

[Helen]

----- Louise wrote: -----

I have a worksheet with thousands of postcodes on.
However, the postcodes have been entered as one word, ie.
M445WE instead of M44 5WE. Is there a way I can ask
Excel to put a space after the first 3 characters?

Any urgent help would be appreciated.

Thank you.

Louise

 

[Louise]

Excellent!! Thanks very much.

Louise

-----Original Message-----
Hi

One problem with this is that not all UK postcodes are 3 characters then a
space and then 3 characters. One way to do what you ask, though, is with a
helper column with
=LEFT(A1,3)&" "&RIGHT(A1,3)
You can then autofill this down. If you happy with it then you should use
Edit / Copy on the rangeand then Edit / Paste Special / Values to fix the
values in place. It is then safe to delete the original data.

Hope this helps.
--
Andy.





.

 

[Gord Dibben]

Louise

If you would prefer a 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