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 this happens
- It is the same cause as scientific notation, seen from the other end. Read as a number, 00123 and 123 are the same value, so the zeros are not data to be preserved; they are formatting to be normalised away.
- 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. The test is whether adding two of them means anything.
- Custom-formatting the column back to five digits with leading zeros makes the display right and the value wrong. That is often worse than the visible problem, because it now survives a spot check and fails at export.
What to do right 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. Guessing the width invents data.
- Do not rely on a display format to carry meaning. Anything downstream reads the value, not the formatting, and the two are now telling different stories.
Stopping it on the next file
Type the field as text and the zeros are never up for discussion, because nothing tries to read the cell as a quantity. Required catches the ones that arrived empty, and unique catches the ones that arrived twice.
The inventory count template is a sheet with those rules already set, if you want to see the shape of one before building your own.
Other things that go wrong
The supplier sent the same SKU twiceOne product code appears on more than one row of the same file, sometimes with two different prices. Nothing in the file says which of them is current.My price column will not add upA column that plainly contains prices sums to zero, or sums to less than it should. Some cells are left-aligned where others are right-aligned.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. Totals are wrong or the import refuses the whole column.
Define the sheet once
Name your fields, and every file after that is matched to them and checked cell by cell before a row is allowed to land.