The leading zeros disappeared from my spreadsheet
Product codes, zip codes, or account numbers that started with one or more zeros now start with a digit. 00123 is 123, and 07030 is 7030.
Why it happens
- It is the same cause as scientific notation, seen from the other end.
- Anything whose first character can be zero is a label rather than a quantity: zip codes, phone numbers, bank sort codes, some SKUs, most internal IDs.
- Custom-formatting the column back to five digits with leading zeros makes the display right and the value wrong.
What to do now
- Import with the column set to Text rather than opening the file directly, so the value is never read as a number in the first place.
- If it has already happened, re-pad only when you know the true width for certain, and only for a format that genuinely has one, such as a five-digit US zip.
- Do not rely on a display format to carry meaning.
Next time
Type the field as text and the zeros are never up for discussion, because nothing tries to read the cell as a quantity.
Start from the inventory count template.
Related problems
The supplier sent the same SKU twiceOne product code appears on more than one row of the same file, sometimes with two different prices.My price column will not add upA column that plainly contains prices sums to zero, or sums to less than it should.Merging contact lists gives me duplicatesThree lists went in and the combined file has the same people more than once, often with slightly different spellings, capitalisation, or company names.“N/A” and dashes in a column that should be numbersA numeric column contains “N/A”, “-”, “n.a.”, or “(blank)” alongside real figures.