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!

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

Can you not EditReplace the ' with nothing just to leave you the numbers?

"Tray" wrote:

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!

On Wed, 2 Feb 2005 19:03:06 -0800, "Tray"
wrote:

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!

What version of Excel?

When you say "close inspection" are you looking at the entry in the Cell, or
are you looking at the entry in the Formula Bar?

If the former, then Edit/Replace ' is how to start.

If the latter, then the numbers are stored as TExt and need to be converted to
numbers.

To do so:
1. Select a blank cell.
2. Edit/Copy
3. Select your phone numbers.
4. Edit/Paste Special/Operation Add/<OK <Esc

You may then format the numbers as above.



--ron