5 ms·

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 wi

by 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"?

by ninju 2y ago

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