Custom Number Query

[Minnie M]

I’m 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?

 

[Gary''s Student]

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.

 

[Minnie M]

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

Thanks for your help.