MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Leading zeros


Posted by Michael O'Neill on January 25, 2001 11:50 AM

Is there a way to defeat the suppression of leading
zeros?

It's a library application, and I wish to display
Dewey Decimal numbers, such as 636.2, 398.2, etc.
The problem arises with 001.64, 004.6, etc. With
LH justification, the decimal points don't line up.

Any ideas?


Posted by Tim Francis-Wright on January 25, 2001 11:56 AM


You could use a (custom) number format like 000.0###
which forces 3 numbers to the left of the decimal
point, and at least one to the right.

If you use 000.####, it shows 001.4 or 398.2
but makes 796 appear as 796. instead of just 796
--kind of annoying but perhaps moot in your case.

Hope this helps.

Posted by Mark W. on January 25, 2001 12:45 PM

Michael, since the indexes of the Dewey Decimal system are
not really numbers (i.e., you're not gonna perform math
on them) you should really format that column as "Text"
which will treat entered values such as "001.64" as a text
string and preserve the leading zeros. Alternately, you
could enter them as '001.64.

Posted by Mark W. on January 25, 2001 1:48 PM

Oh yeah, Michael there's another reason you want these
values to be text. You can't use several of the
AutoFilter conditions (e.g., "begins with", "contains",
"ends with") that are used with text strings. You
also couldn't effectively use the text wildcards,
"?" and "*". For example, you could filter for all
the values that begin with a 100's value by using
the condition, "?00.*". This would list 100.45,
200.50, etc. Try that on a list of numeric values
and you get zilch!

Posted by Michael O'Neill on January 26, 2001 10:55 AM

Thanks for the responses; both suggestions
worked fine, and I appreciate the assistance.