Custom Format

[Guest]

If I have a cell that contains text: 0012222 and I wish to apply custom
formatting to it like this: ###-#### I would expect the cell to look like
this: 001-2222 but if the text is already in the cell the view of the cell
does not change. But if I blank the cell out and then type 0012222 it
appears as 001-2222. Any idea why this is and how do I get around it so I
can format cells that are already containing data?

 

[Dave Peterson]

If the entry was made when the cell was formatted as text, then the value in
that cell is still text after you changed to the custom format. And text values
won't use this custom number format.

One way to fix this is to
select an empty cell
edit|copy
Select the range of offending cells
edit|paste special|check Add and values

 

[David McRitchie]

Hi Mike,
In the back of your mind, you're probably pretty clear on how
it works, but to be more specific.

Changing the format between text and number in either direction has
no effect on the existing formatting of the cell until the data in the
cell is reentered. The simplest way to reenter same data for
a single cell is to use F2 then Enter.

A single quote in front of data will override formatting and will
be a text entry.

Changing the format of a cell actually formatted as numeric
(not as text) to another number format will be effective immediately.

You may find items of additional interest at
http://www.mvps.org/dmcritchie/excel/formula.htm
http://www.mvps.org/dmcritchie/excel/join.htm#debugformat
and the topic below that as well (#reenter) on reentering.