How Excel destroys your GTINs, and how to get them back

Updated August 2026

Opening a CSV in a spreadsheet can invalidate the GTINs inside it before anyone edits a single cell. The conversion happens on open, silently, and the file looks normal afterwards.

There are three separate mechanisms, and they do not have the same consequences. One is always recoverable, one may or may not be depending on what actually reached the file, and one destroys data in memory. Knowing which you are looking at decides whether you can fix the file or have to go back to the source system.

1. Leading zeros stripped, on open

Excel treats a GTIN as a number, and numbers do not carry leading zeros. 036000291452 becomes 36000291452, silently, the moment the file opens.

Eleven digits is not a valid GTIN length, so a validator will reject it. The good news is that this damage is fully reversible. Zero padding never changes the check digit, so every plausible reconstruction converges on the same identifier:

You do not need to know which width the value started at. Pad to any valid length, confirm the check digit with the GTIN checker, and store the GTIN-14 form.

2. Scientific notation, on display and sometimes in the file

In the General cell format, Excel switches large integers to scientific notation. A twelve digit UPC-A such as 885911123457 displays as 8.85911E+11.

Seeing that on screen does not by itself mean anything was lost. Scientific notation is a display format, and the workbook still holds the full number behind it. Whether your data survived depends on what was written into the CSV, and that is a different question from what the screen shows.

So check the file, not the screen. Reopening the CSV in Excel just re-applies the same display and tells you nothing. Open the raw file in a text editor, or run head on it, and read the characters actually stored on that line:

In the second case the identifier really is unrecoverable from that file, and it is worth being precise about why. Six trailing digits are missing, which leaves a million candidates, and because exactly one final digit in every ten satisfies the check, that narrows to exactly one hundred thousand valid GTINs. No arithmetic picks the right one out of those.

Reformatting the column as Text after the fact does not help there either: it changes how a value is displayed, and the value it is displaying is already 8.85911E+11.

The rule is to check the raw bytes before concluding anything. If the digits are present, fix how the file is imported. If they are not, that file is evidence of a problem rather than a source of truth, and the identifiers should be re-exported from the system that issued them.

3. The fifteen digit ceiling, in memory

Excel stores numbers with fifteen significant digits and replaces everything past the fifteenth with zeros. This one is worth understanding precisely, because it affects GS1 identifiers unevenly:

An SSCC of 106141411234567897 becomes 106141411234567000. The number still looks like an SSCC, it is the right length, and it is the wrong pallet. This is the failure mode that is hardest to spot by eye, because nothing about the value looks damaged.

Your GTINs are safe from this specific mechanism. Your pallet labels are not.

How to tell what happened to a file someone sent you

Open the CSV in a text editor rather than a spreadsheet, then look for three things:

• E+ anywhere in the identifier column. Scientific notation damage. Unrecoverable from this file.
• Identifier lengths that are not 8, 12, 13, or 14. Usually stripped leading zeros. Recoverable.
• Runs of trailing zeros on long identifiers. Possible precision loss on an SSCC or a serial. Check against the source.

Opening the CSV in Excel to inspect it re-applies the same conversions to your copy, which is why the text editor step matters.

Prevention

• Never open a product CSV by double clicking it. That path gives you no import dialog and the conversion happens before you see the sheet.
• Use Data, then From Text/CSV, and set the identifier column to Text in the import dialog. This is the only step that reliably prevents both failure modes.
• Format the column as Text before pasting anything into a new sheet.
• On export, open the resulting CSV in a text editor and confirm the digits are still there. This takes ten seconds and is the only real verification.
• Google Sheets behaves the same way. Format, then Number, then Plain text, before pasting. Its import dialog also has a "Convert text to numbers, dates, and formulas" toggle that must be off.

The pattern worth internalizing: formatting as Text protects values that are not yet in the cell. Applying it after the fact formats the damage.

Recovery, and its honest limit

For stripped leading zeros, pad to a valid length and verify. A passing check digit is strong evidence the identifier was recovered, and it is not proof. The check digit misses about 11% of adjacent transpositions, so it can confirm a value is well formed without confirming it is the right value. The GTIN checker explains exactly which cases it cannot see.

For scientific notation and precision loss, there is no recovery from the file. Go back to the system that issued the identifier.

The durable fix is to stop treating a spreadsheet as the system of record for identifiers. Where a spreadsheet has to be in the loop, store the GTIN-14 form everywhere, because a fourteen digit value with its zeros already present has nothing left to strip.

Checking a whole catalog

The free readiness audit applies these checks to every row: invalid lengths, scientific notation damage, failed check digits, and duplicates created when two differently damaged rows collapse onto the same value. It reports the rows that fail rather than a score, so you can hand the list to whoever owns the source data.