Custom Number Query
 

Im using Excel 2002 and have a list of phone numbers as 12345 678 900 and
want to change them to 1234 5678900.

When I custom format as 0000 0000000, some of the cells change to that
format but others remain the same. Could there be some other formatting in
the cells that is over-riding the custom number format, and if so, how do I
find it?

Custom Number Query
 

If there are real spaces in your data, Excel sees it as text and the custom
format will not apply.

=--SUBSTITUTE(A1," ","")

Will convert them into real numbers and the custom format will apply.


The alternative is to make all the data text and insert/delete spaces
yourself.
--
Gary''s Student - gsnu200736


"Minnie M" wrote:

Im using Excel 2002 and have a list of phone numbers as 12345 678 900 and
want to change them to 1234 5678900.

When I custom format as 0000 0000000, some of the cells change to that
format but others remain the same. Could there be some other formatting in
the cells that is over-riding the custom number format, and if so, how do I
find it?

Custom Number Query
 

Hi Gary's Student, I did a Find to delete the spaces and then the formatting
worked.

Thanks for your help.

"Gary''s Student" wrote:

If there are real spaces in your data, Excel sees it as text and the custom
format will not apply.

=--SUBSTITUTE(A1," ","")

Will convert them into real numbers and the custom format will apply.


The alternative is to make all the data text and insert/delete spaces
yourself.
--
Gary''s Student - gsnu200736


"Minnie M" wrote:

Im using Excel 2002 and have a list of phone numbers as 12345 678 900 and
want to change them to 1234 5678900.

When I custom format as 0000 0000000, some of the cells change to that
format but others remain the same. Could there be some other formatting in
the cells that is over-riding the custom number format, and if so, how do I
find it?