Why Excel strips leading zeros from SKUs, barcodes and postcodes
Short answer
Excel converts anything that looks like a number when you open a CSV, paste or type it: 00123 becomes 123, a 13-digit barcode becomes 4.00638E+12, and digits after the 15th turn into zeros. Keep codes as text: import the column with type Text (Data › From Text/CSV › Transform), or switch off the conversions in Excel's Automatic Data Conversion settings (Microsoft 365 and Excel 2024).
What Excel does to codes
| In the file | Excel shows | Why |
|---|---|---|
00123 (SKU, postcode) | 123 | Numbers don't have leading zeros |
4006381333931 (EAN-13) | 4.00638E+12 | Long numbers display in scientific notation |
1234567890123456 (16 digits) | 1234567890123450 | Excel keeps 15 significant digits |
1E5 (a part number) | 1.00E+05 | Digits around an “E” read as scientific notation |
This hits warehouses and shops hardest: SKUs, EAN-13 (13 digits) and UPC-A (12 digits) barcodes, postcodes such as 02134, phone numbers, account and order numbers. None of them are quantities, but they look like numbers.
How do I stop it when opening a CSV?
- Don't double-click the file. In Excel, choose Data › From Text/CSV (Windows) or Data › Get Data › From Text/CSV (Mac, Microsoft 365).
- In the preview, choose Transform Data instead of Load.
- Select the code columns, set Data Type › Text, and choose Replace current if asked.
- Close & Load. The codes keep every digit.
Turn the conversions off for good
Excel for Microsoft 365 and Excel 2024 (Windows and Mac) let you switch these conversions off:
- Windows: File › Options › Data › Automatic Data Conversion.
- Mac: Excel › Preferences › Edit › Automatic Data Conversion.
Untick Remove leading zeros and convert to number and Keep the first 15 digits of long numbers and display in scientific notation if required (and, for part numbers like 1E5, the option about the letter “E”). These settings apply when you open CSV or text files, type and paste — not to Power Query imports, which use the column types you set (Microsoft Support).
Can I recover what was lost?
- Leading zeros, fixed length: if every code should be, say, 5 digits, give the column the custom number format
00000— or make a text copy with=TEXT(A2,"00000"). For 13-digit EANs use 13 zeros. - Scientific notation: format the column as Number with 0 decimals and widen it. The digits are still there if the code had 15 digits or fewer.
- Digits after the 15th: gone — Excel stored zeros. Re-import the original file with the column as Text.
A checklist for SKU and barcode sheets
- Prefer .xlsx files over CSV: cells stored as text stay text.
- When you must use CSV, import with the code columns as Text.
- Sort by the code column after importing: a run of codes that suddenly got shorter shows lost zeros.
- For lookups (
XLOOKUP,VLOOKUP), make both sides text — a number123doesn't match the text00123.
What QuickScan does with codes
QuickScan's Excel files keep codes as text: anything with a leading zero (007, 02134), whole numbers of 12 or more digits (barcodes, account numbers) and anything mixed with letters. Only plain quantities and amounts become numbers. So a scanned stock list or delivery note opens with its SKUs intact. Its CSV files are plain text like any other CSV — open them with the import steps above, or export .xlsx. See printed stock lists to Excel.
On a computer? Point your iPhone's camera at the code to open QuickScan in the App Store.
3 free exports, then a one-time unlock. No subscription.
Questions
Why does Excel remove leading zeros?
Excel treats 00123 as the number 123 when you type it, paste it or open it from a CSV, and numbers don't keep leading zeros. Store codes as text — import the column as Text, or turn off the leading-zero conversion in Excel's Automatic Data Conversion settings.
Why does a barcode show as 4.00638E+12?
A 12- or 13-digit code is a big number to Excel, so it shows it in scientific notation. Widening the column or formatting it as Number (0 decimals) shows the digits again — as long as it has 15 digits or fewer.
Can I get lost digits back?
Zeros at the start, yes: they can be padded back with a number format or TEXT() if you know the length. Digits after the 15th of a long number, no: Excel replaced them with zeros. Re-import the original file with the column as Text.
Does an .xlsx file have this problem?
Not if the cells are stored as text. The conversion happens when Excel reads typed, pasted or CSV values. QuickScan's Excel files store codes with leading zeros and long codes as text, so they open intact.