Hi!

Here's one solution if the lenght of these "numbers" are
all the same.

In the adjacent column enter this formula and copy down as
needed:

=LEFT(A3,4)&" "&RIGHT(A3,6)

Now, select the entire range of values in that adjacent
column and do Copy/Paste Special/Values.

Then you can delete the original list.

You could also use a custom format like you thought but do
all of the numbers start with a leading zero?

If so, first, you need to convert the text numbers to
numeric numbers.

Select an empty cell and copy it.
Select the range of text numbers and do Paste Special/Add.
Then you use a format like 0### ######.

Biff
-----Original Message-----
I have been provided a list of phone numbers in an excel
spreadsheet. I am
trying to add a space in between the code and the
remaining number
eg: 0412346567 becomes 0412 346567
I would usually go to format, custom and enter ####
######.
But after closer inspection, all the numbers have '
before them (although it
does not show in the cell). So I am unable to format them
using the usual
method.
Is there any way I can do this. Excel Help said that if I
go to Tools,
Options, Transitions, and untick "Transitions Navigations
Keys", then the
apostrophies would be removed. However, it is already
unticked!
.