Re: Leading Zeros?

open original image

Thanks for replies on this issue. No one said what I was hoping someone

> would tell me, however, namely that avoiding the use of leading zeros
> was a well-known design principle. Advice like
> "As a matter of good application design (or at least, if you'd just
> as soon avoid all kinds of unnecessary complications) don't design
> a system that employs labels, or ID numbers, or "article numbers"
> in which leading zeros are significant or even necessary"
> still seems to me a sensible choice, however.

That's fine advice to the people designing the labeling system in the first place. But if you're implementing a database that manages data from a preexisting schema, you don't usually have the luxury of changing the way ID numbers are assigned. If the system already makes use of significant leading zeroes, you have to accomodate that.

Barry Margolin, snipped-for-privacy@alum.mit.edu Arlington, MA

>>> 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,

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

Format the cell as TEXT **first**, then enter 00038 or whatever. Exactly what you entered will retain.

>>> 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.
>> If you treat the cell as *TEXT* then _exactly_ what you enter =will= >> be preserved.
> 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.)
> In any case, I think my statement above as to the __default__ behavior
> of Excel is correct ... ?

To Robert Bonomi:

I have tried emailing you privately, but my messages always bounce.

I have seen many of your posts in the TELECOM Digest. Your comments would be more effective if you didn't overuse punctuation marks. You are obviously trying to add emphasis to your writing, as if you are speaking, but excessive and misused punctuation marks just slow down and distract the reader. Writing and speech are two different things.

For example, this paragraph is wild with unnecessary punctuation and capitals:

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>

This would be better:

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>

Most dictionaries have a section about proper use of punctuation and capitalization. Please take a look.

I repeat: Writing and speech are two different things.

I'm not a writing expert; just an engineer interested in reading your posts in TELECOM. When I see one of your posts now, I tend now to skip over it because of the difficulty I anticipate in reading it. (All you language experts out there are now free to critique this message.)

Respectfully,

Jeff