Excel 2002; cell format confuses Custom and Special

[Guest]

This is a weird one. I manipulate date received from vendors, and I've seen
my share of kinks, but this has never happened before. A worksheet has a
column of Tax ID numbers, and the format of all the cells is "General"; my
routine is to find and destroy all dashes, and then format the cells to
"Custom" (000000000.) That will provide the preceding zeroes that are
frequently lost in handling, and I find that the display and export of the
data works for my purposes. But this time, when I select all the cells
(whether I select the column or just the cells) and format Custom, the
entries that need preceding zeroes do not get them; and when I call the
format dialog again, it shows the format as "Special", language Portuguese,
type Numero da Seguranca Social. The sheet is not protected, and I can find
no conditional formatting. Does anyone have an idea where this is coming
from? Thanks--

 

[Gord Dibben]

Sounds like the "numbers" are text which cannot be formatted for leading zeros.

Format all the General then copy an empty cell.

Select the "numbers" and Edit>Paste Special(in place)>Add>OK>Esc.

Now try the leading zero formatting.


Gord Dibben MS Excel MVP

 

[Guest]

Thank you, Gord; that did the trick for entering the preceding zeroes (I use
multiply, with a cell containing "1", for dates that need to be checked; this
appears to do the same thing.)
But that darn "Special" format still shows up after I've formatted the cells
to "Custom"; it doesn't appear to hurt anything, but it makes me nuts that I
can't figure where it's coming from.
Peace--
TeeArr