Re: Leading Zeros?

B

Barry Margolin 21 years ago

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

*** PLEASE post questions in newsgroups, not directly to me ***

Vote

D

Dave Close 21 years ago

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

If you define a spreadsheet cell as "text", you can enter all the leading zeros you like and keep them. Unfortunately, the default setting is "general", which is neither text nor numeric: it means treat the value as numeric if it only contains digits, text otherwise.

If you have data where leading zeros have been lost, it isn't difficult to restore them on retrieval using a print format statement of whatever style is appropriate for the system you are using.

Dave Close, Compata, Costa Mesa CA "Politics is the business of getting snipped-for-privacy@compata.com, +1 714 434 7359 power and privilege without snipped-for-privacy@alumni.caltech.edu possessing merit." - P. J. O'Rourke

Vote

R

Robert Bonomi 21 years ago

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

Vote

M

Matt B 21 years ago

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 ... ?

Vote

J

Jeffrey Mattox 21 years ago

[Please withhold my email address for spam avoidance reasons.]

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

[TELECOM Digest Editor's Note: Dear Respectful Jeff, I have often wondered if I could require writers here to be familiar with and make use of the _Chicago Manual on Style_ as it would make my job a lot easier also. PAT]

Vote