5 min read
EAN, GTIN and lost zeros in Excel: keep your codes intact
By Quentin Delepierre, Salesforce Commerce Cloud consultant
You open a product file in Excel and your barcodes turn into "3.61235E+12". It's not just a display issue: when the file is saved as CSV, the digits are lost. Here's what happens and how to avoid it.
The symptoms
- An EAN-13 such as 5060155242347 shows as "5.06016E+12".
- A UPC starting with zero, 060155242347, becomes 60155242347. An item code "00123" becomes 123.
- A size "1-2" becomes "2-Jan". A date "2026-09-01" is shown in your regional date format.
Why the digits are lost
Opened by double-click, a CSV is read by Excel as if you typed each value: anything that looks like a number becomes a number, anything that looks like a date becomes a date. A number has no leading zero, and beyond 11 digits Excel shows it in scientific notation.
While the file is open, the full value still exists in the cell. But when saving as CSV, Excel writes what it displays: "5.06016E+12" goes into the file, and the last digits of the code with it. The file can no longer be repaired from itself.
The right way to open the CSV
- In Excel, open a blank workbook, then Data › From Text/CSV, and pick the file.
- Click "Transform Data". In the editor, select the code columns (EAN, GTIN, UPC, SKUs) and give them the Text type. Replace the type if Excel asks.
- Click "Close & Load": the codes arrive as they are, zeros included.
- After your edits, save as CSV: text columns are written without conversion.
Excel 365 and 2024 settings
Excel for Microsoft 365 and Excel 2024 (Windows and Mac) have automatic data conversion options: File › Options › Data on Windows, Excel › Preferences › Edit on Mac. Turn off the removal of leading zeros and the conversion of date-like combinations: leading zeros and sizes such as "1-2" are then kept, even by double-click.
These options don't protect an EAN-13: it stays a number, shown in scientific notation. For 12- and 13-digit codes, importing as Text is still needed.
What ExcelifyXML does
- On transformation, the result screen lists the columns Excel is likely to change: leading zeros, codes of 12 digits or more, values Excel would take for dates.
- On reconstruction, if a code turned into scientific notation or a date changed format, reconstruction stops and shows the columns, rows and an example. You fix it, or rebuild anyway knowingly.
- Lost leading zeros don't block: "123" may be a value you typed. They're flagged from the transformation on; check them before re-importing.
Variant: the standalone Excel workbook
In the standalone Excel workbook (2 credits), all data is written as text: EANs, leading zeros and dates stay exactly as in the XML, with no setting or special import.
It already happened: how to fix it
Lost digits can't be found in the damaged CSV. Go back to the original data.csv, which is in the downloaded bundle: open it with the method above, copy the code column and paste it into your edited file, as long as you haven't sorted or deleted rows in the meantime. Otherwise, redo your edits on a CSV opened correctly.
Turn your XML into a CSV, with risky columns flagged:
Transform a file