You export a file, open it to check it, and the data is different. Product code 00417 is now 417. The reference MAR1 is now a date in March. A 19-digit order number ends in zeros it never had.
None of this is a bug, and none of it is random. Excel applies a fixed set of conversions when it reads a value, and each one has a rule you can predict. The useful question is not “why did it break” but which conversion did I hit, because that decides whether the original is still there.
The four conversions
Excel names these itself, in the settings described below. Between them they account for almost every “Excel changed my data” report.
Leading zeros are removed. 00123 becomes 123. Anything that looks numeric is stored as a number, and a number has no concept of leading zeros. This hits postal codes, product codes, account numbers, and any identifier that was zero-padded to a fixed width.
Long numbers lose their tail. Excel stores numbers with 15 digits of precision. A 16-digit value keeps its first 15 and the rest become zeros: 12345678901234567890 is stored as 12345678901234500000, and displayed as 1.23457E+19. Order numbers, card-like references and long database IDs land here.
Anything with an E in the middle becomes scientific notation. 1E5 is read as 100,000. Chemical identifiers and short alphanumeric codes get caught by this one.
Continuous letters and numbers become dates. MAR1 becomes 1 March. This is the conversion that made the gene-naming community rename its genes, because SEPT1 and MARCH1 were unusable in a spreadsheet.
Which ones destroy the value and which only hide it
This is the distinction that matters, and it is not obvious from looking at the screen.
Leading zeros and dates are reversible. The underlying value changed type, but no information about the characters was thrown away — 417 still contains everything 00417 did apart from padding you can reapply, and a date holds the day and month that MAR1 encoded. Painful, but recoverable if you know the original format.
The 15-digit truncation is not. Those digits are gone. Excel did not hide them or reformat them; it rounded the number and kept the result. No amount of reformatting the cell brings them back, because the cell no longer knows what they were.
That is why a file with long identifiers must never be opened casually “just to look”. Looking is the destructive step, and saving afterwards writes the loss to disk.
The settings, and the one they do not cover
Recent Excel lets you switch these off. In File → Options → Advanced, under Automatic Data Conversion, there are four checkboxes matching the four conversions above. They exist in Excel for Microsoft 365 and Excel 2024, on Windows and Mac.
Turn them off and Excel keeps such values as text. This is worth doing and most people have never seen the screen.
But read the fourth one carefully. Disabling “Converting a continuous string of letters and numbers to a date” stops JAN1 becoming a date. Microsoft’s own documentation notes that values with a space or a separator — JAN 1, JAN-1 — may still be treated as dates anyway. So the setting handles the tidy case and leaves the two forms you are most likely to actually have.
There is a second limit: the settings live on your machine. The colleague who opens the same export on their laptop still has the defaults, and the file comes back converted.
Why it happens on open, not on save
A CSV or TSV is a text file with no type information in it. 00417 is five characters; nothing in the file says whether that is a number, a code, or a string that happens to be digits. Excel has to guess a type for every cell before it can display anything, and those four rules are the guess.
This is why “I only opened it” is not a defence, and why the damage is already done by the time you notice. The conversion happens during the read. Saving does not cause it — saving only makes it permanent.
It also explains why the file is fine everywhere else. The database, the script and the importer that consume it all read the characters as characters. Excel is the only step in the chain that decides your identifier is a quantity.
Getting the original values back
In order of preference:
- Go back to the source export, if one exists and has not been overwritten. This is the only route that is guaranteed whole.
- Re-import the original file properly, forcing the affected columns to Text. This works if the file itself was never resaved from Excel.
- Reconstruct, only for the reversible cases: re-pad the leading zeros to a known width, convert dates back to the code they encoded. Check a sample against the source, because
MAR1andMAR10do not round-trip the same way. - For truncated long numbers, there is nothing to do. The digits are not recoverable from the file.
If you are about to open an export at all, opening a CSV without breaking it covers the import route that avoids all four conversions.
Doing it here
The editor on this site reads TSV and CSV cell values as text, without date or number conversion: 00417 stays five characters and a 19-digit identifier keeps all 19 digits. Check the detected encoding and the interpreted cells before exporting.
That is a deliberate limitation rather than a missing feature: a tool that interprets values is a tool that can misinterpret them, and this one exists for files where the characters are the point.
TSV and CSV export checks that values and row structure can be reopened. Downloads use UTF-8; TSV uses tabs and CSV uses commas. Optional quotes can change. The export dialog shows record line endings, BOM and the final line break, declares conversions and blocks unsafe output. Nothing is uploaded: your browser reads and writes the file.