TELECOM Digest OnLine - Sorted: Re: Leading Zeros?

Re: Leading Zeros?

Robert Bonomi (
Fri, 18 Feb 2005 09:10:27 -0000

In article <>, AES
<> wrote:

> In article <>,
> (Robert Bonomi) wrote:

>>> citations to work properly. A few quick tests show that the default
>>> behavior is to automatically and irretrievably discard leading zeros
>>> on integer numbers keyed into an Excel worksheet cell or a Mathematic
>>> list.

>> Note: *BAD* test.

>> You can select the 'formatting' on a cell, column, or entire
>> worksheet, to display numbers with leading zeroes. If you treat the
>> cell data as a 'number', it will be 'left filled' with zeroes to the
>> number of positions you specify.

> I just once again opened Excel: Mac 2004, selected a cell at random,
> typed in "0038", and hit the Enter key. The selection dropped down to
> the cell below and left "38" displayed in the cell in which I'd made
> the entry. I re-selected that cell and looked at the entry field up
> above where the contents are displayed; it also held "38".

> I then played some with the "Number" formats for that cell, and was
> unable to find any kind of Custom number format that gave me back the
> leading zeros -- but maybe I missed something??? (The "ZIP Code" format
> did give me leading zeros: "00038" to be specific, but I don't think
> that would be helpful.)

What is 'right' depends on "what you want out".

If you always want 4 digit numbers, with leading zeros as needed to
reach that specific size, then you select 'custom' for the format,
*AND* type "0000" (without the quotes in the field specification on
the upper right side of that pane.

If you want "exactly as entered", you select the "text" type from the
list on the left side of pane. You have to do this *BEFORE* you enter
the data in the field, because *this* setting affects the manner in
which the data is _stored_, not 'just' the manner of display.

> In any case, I think my statement above as to the __default__ behavior
> of Excel is correct ... ?

True. The point being that the 'default' behavior is *NOT*
appropriate if you want 'stored "exactly as entered"'. Excel provides
the 'text' field attribute for those situations. You simply have to
'think ahead', and specify the desired attribute *before* entering the
data. Either by setting the 'format' attribute _first_, *or* by
'prefixing' the data value with the special 'flag' character (an
apostrophe, or 'single-quote', i.e, "'") that tells Excel to 'store
this *exactly*as*I*entered*it*.

In high school, the wood-shop teacher had a saying: "you have to
out-think the materials you work with." This is true, whether the
materials are a block of wood, or a computer.

If you want data preserved "exactly as entered', all you have to do is
*SAY*SO*. If you don't say, Excel 'guesses', based on what is
"correct _most_ of the time". If it 'guesses wrongly', that is not a
failing of the program -- after all, "ROM" in a computer does *NOT*
mean "read operator's mind". <grin>

Post Followup Article Use your browser's quoting feature to quote article into reply
Go to Next message: Matt B: "Re: Leading Zeros?"
Go to Previous message: Dave Close: "Re: Leading Zeros?"
May be in reply to: AES: "Leading Zeros?"
Next in thread: Matt B: "Re: Leading Zeros?"
TELECOM Digest: Home Page