4 ms·

The one that always bites me is Excel truncating the leading zero in US zip codes (they start with 0 in the Northeast US). I’m wondering if that would have hap

The one that always bites me is Excel truncating the leading zero in US zip codes (they start with 0 in the Northeast US). I’m wondering if that would have happened if Microsoft was located in Boston instead of Seattle.

ninju 2y ago
That because Excel defaults to treating numeric data as a number and leading zeros are extraneous and it will strip them off before storing the value (and it will right justify the display). The root issue is that zipcodes though numeric in content (at least in the US) should not be treated as number (data type) but instead as a text (string) value To tell Excel to treat this numeric data as a string you to either * Precede the value with a single quote (') - Excel will treat the rest of the data as a string (and won't hide the leading zeros) * Before entering the value set the format to TEXT which will tell Excel to take the entry verbatim with no inferring what the data represents (i.e. a number or date)
- mattigames 2y ago
  It is the fault of zip codes, they should have been prefixed with the state code from the start (CA for California and so on), that's one of the reasons secret 2FA codes are sometimes preceded with one or two letters (e.g. Facebook uses FB)
  - windhaven 2y ago
    The issue with that is that ZIP codes don’t map physical locations, they map the hierarchy of how the mail system does routing down to each post office and were introduced in the 1960s [0]. As a result, doing something “from the start” wouldn’t involve baking in comparability with the quirks of a piece of software written decades later, and you’d also have issues with, for example, single zip codes spanning multiple states. [0]: https://en.m.wikipedia.org/wiki/ZIP_Code https://en.m.wikipedia.org/wiki/ZIP_Code
    - mattigames 2y ago
      Well, then something that made more sense, like the letter Z for zip code.
- magicalhippo 2y ago
  And instead of just copy/pasting tabular data, use the Text Import Wizard (my translation) under the Paste drop-down menu, and ensure appropriate columns are marked as text.
- johngladtj 2y ago
  Leading zeros are not extraneous and should not be removed though
  - ziml77 2y ago
    For numbers they are extraneous, for strings of digits they are not.
    - wruza 2y ago
      They are never extraneous, cause no automatic calculation process produces them. If they are there, it's intentional.
  - otikik 2y ago
    Aren't they exactly the definition of "extraneous"?
mrgoldenbrown 2y ago
Zipcodes aren't really numbers, they are strings. You can't meaningfully do math on zipcodes, so better to just treat them as text.
- recursive 2y ago
  Yes, that's the point. But excel just incorrectly determines what you meant, and corrupts your data.
  - ziddoap 2y ago
    >But excel just incorrectly determines what you meant How would you, if you were programming excel, determine whether the 5-digit number entered with a leading 0 is meant to be a zip code or not?
    - sosborn 2y ago
      Would keeping the leading zero cause any problems with future calculations?
    - recursive 2y ago
      I would not make any default determination until and unless there was a proactive user action. If there is any value that doesn't round trip through string serialization, don't allow it to be coerced without the user deciding to allow it, explicitly.
      - dreghgh 2y ago
        You know that many users of Excel enter numbers into the cells and then do arithmetic calculations with them right?
        deleted 2y ago
        [deleted]
        recursive 2y ago
vikingerik 2y ago
The postal service has learned and knows to compensate for this. If you mail something with four digits for the zip code, it will be treated as if it had a leading 0 and routed to the northeast.
bombcar 2y ago
Zip codes I sleep. You don't want to know how many phone numbers in various databases show up in exponential notation. Not gonna talk about it.
- deleted 2y ago
  [deleted]
Imustaskforhelp 2y ago
Oh the hatred I had , I was making a financial estimate for my maternal uncle who is an engineer and I am not sure what the issue was , maybe it was leading 0 part because ending 0 part would be preserved , it happened like 2 years ago. Also , I think the problem had actually been of libreoffice or whatever , oh yeah it was .00 , I wanted that .00 but it just removed it. Like I said , I don't remember it. and I don't even remember how I fixed it , but I only remember the pain because it felt so simple yet it doesn't .... , I really wanted to use some python esq interface on something like libreoffice as well because my uncle had a pdf which had a column for the material code (like something like 1.1.2) and then it had a description and a name and I Had to copy material code from 1.1.2 and then paste it. And he said that there was some other engineer in his department who had actually figured out where he would only type in 1.1.2 for example and on the next column, it would show up automatically , It was kind of crazy but I was thinking of creating a cloud service for such engineers which only had this (are there excel extensions ?) , or whatever because there are so many such engineers & my uncle would've definitely paid 10$ if it made his job easier since he always used to force some of us kids to do it for him. He just couldn't figure out how to do it himself and I don't blame him.

ninju 2y ago

That because Excel defaults to treating numeric data as a number and leading zeros are extraneous and it will strip them off before storing the value (and it will right justify the display). The root issue is that zipcodes though numeric in content (at least in the US) should not be treated as number (data type) but instead as a text (string) value To tell Excel to treat this numeric data as a string you to either * Precede the value with a single quote (') - Excel will treat the rest of the data as a string (and won't hide the leading zeros) * Before entering the value set the format to TEXT which will tell Excel to take the entry verbatim with no inferring what the data represents (i.e. a number or date)

mattigames 2y ago
It is the fault of zip codes, they should have been prefixed with the state code from the start (CA for California and so on), that's one of the reasons secret 2FA codes are sometimes preceded with one or two letters (e.g. Facebook uses FB)
- windhaven 2y ago
  The issue with that is that ZIP codes don’t map physical locations, they map the hierarchy of how the mail system does routing down to each post office and were introduced in the 1960s [0]. As a result, doing something “from the start” wouldn’t involve baking in comparability with the quirks of a piece of software written decades later, and you’d also have issues with, for example, single zip codes spanning multiple states. [0]: https://en.m.wikipedia.org/wiki/ZIP_Code https://en.m.wikipedia.org/wiki/ZIP_Code
  - mattigames 2y ago
    Well, then something that made more sense, like the letter Z for zip code.
magicalhippo 2y ago
And instead of just copy/pasting tabular data, use the Text Import Wizard (my translation) under the Paste drop-down menu, and ensure appropriate columns are marked as text.
johngladtj 2y ago
Leading zeros are not extraneous and should not be removed though
- ziml77 2y ago
  For numbers they are extraneous, for strings of digits they are not.
  - wruza 2y ago
    They are never extraneous, cause no automatic calculation process produces them. If they are there, it's intentional.
- otikik 2y ago
  Aren't they exactly the definition of "extraneous"?

mrgoldenbrown 2y ago

Zipcodes aren't really numbers, they are strings. You can't meaningfully do math on zipcodes, so better to just treat them as text.

recursive 2y ago
Yes, that's the point. But excel just incorrectly determines what you meant, and corrupts your data.
- ziddoap 2y ago
  >But excel just incorrectly determines what you meant How would you, if you were programming excel, determine whether the 5-digit number entered with a leading 0 is meant to be a zip code or not?
  - sosborn 2y ago
    Would keeping the leading zero cause any problems with future calculations?
  - recursive 2y ago
    I would not make any default determination until and unless there was a proactive user action. If there is any value that doesn't round trip through string serialization, don't allow it to be coerced without the user deciding to allow it, explicitly.
    - dreghgh 2y ago
      You know that many users of Excel enter numbers into the cells and then do arithmetic calculations with them right?
      - deleted 2y ago
        [deleted]
      - recursive 2y ago

vikingerik 2y ago

The postal service has learned and knows to compensate for this. If you mail something with four digits for the zip code, it will be treated as if it had a leading 0 and routed to the northeast.

bombcar 2y ago

Zip codes I sleep. You don't want to know how many phone numbers in various databases show up in exponential notation. Not gonna talk about it.

deleted 2y ago
[deleted]

Imustaskforhelp 2y ago

Oh the hatred I had , I was making a financial estimate for my maternal uncle who is an engineer and I am not sure what the issue was , maybe it was leading 0 part because ending 0 part would be preserved , it happened like 2 years ago. Also , I think the problem had actually been of libreoffice or whatever , oh yeah it was .00 , I wanted that .00 but it just removed it. Like I said , I don't remember it. and I don't even remember how I fixed it , but I only remember the pain because it felt so simple yet it doesn't .... , I really wanted to use some python esq interface on something like libreoffice as well because my uncle had a pdf which had a column for the material code (like something like 1.1.2) and then it had a description and a name and I Had to copy material code from 1.1.2 and then paste it. And he said that there was some other engineer in his department who had actually figured out where he would only type in 1.1.2 for example and on the next column, it would show up automatically , It was kind of crazy but I was thinking of creating a cloud service for such engineers which only had this (are there excel extensions ?) , or whatever because there are so many such engineers & my uncle would've definitely paid 10$ if it made his job easier since he always used to force some of us kids to do it for him. He just couldn't figure out how to do it himself and I don't blame him.