My SKUs turned into scientific notation
A column of product codes, barcodes, or order numbers now reads 1.24E+11. Widening the column does not help, and formatting it as a number gives you a different set of digits than you started with.
Why this happens
- Excel treats anything that looks like digits as a number, and it stores numbers as doubles with 15 significant digits. A 16-digit barcode does not fit, so the digits past the fifteenth are replaced with zeros. That is not a display setting; the value in memory has changed.
- The scientific notation on screen is only the symptom. Formatting the column back to “Number” restores a normal-looking figure, but the lost digits do not come back, because they were discarded on read.
- The underlying mistake is category rather than precision. A SKU is not a quantity: you never add two of them, and a leading zero in one is meaningful. It is a label made of digits.
What to do right now
- Go back to the original file. Once a workbook has been saved with the truncated values, the digits are not recoverable from it.
- Import rather than open: Data → From Text/CSV, then set the code column's type to Text in the dialog before loading.
- If you are the one producing the file, prefix nothing and quote nothing as a workaround. Both travel badly. Send the codes in a column the receiving system reads as text, or send ISO-formatted output the other side controls.
- Check the end of the number, not the start. Truncation eats the least significant digits, so the first several characters still match the real code and a spot check on the first few will pass.
Stopping it on the next file
A text field stays text. A SKU column typed as text is never parsed as a quantity, so a 16-digit barcode arrives with 16 digits and a leading zero survives. Marking that same field unique is what then catches the same code appearing twice in one delivery.
The product catalogue 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 leading zeros disappeared from my spreadsheetProduct 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.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.
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.