Established onExcel 365, Windows: conversion options added 2023Excel 2016 and later: no such optionsGoogle Sheets, web: conversion on paste, per-locale
A spreadsheet decides what a value is at the moment it arrives, and it decides in favour of dates and numbers. The original text is not kept anywhere, so the decision cannot be reversed from the file.
The documented case
Human gene names include short symbols such as SEPT2 and MARCH1. Typed or pasted into a sheet with default settings, these become the second of September and the first of March. Identifiers of the form 2310009E13 are read as scientific notation and become a number.
A 2016 study examined the supplementary spreadsheet files attached to published genetics papers and found the conversion present in a substantial proportion of them, in journals of every rank, over more than a decade.
The response is the part worth knowing. In 2020 the body responsible for naming human genes renamed the affected symbols, so that SEPT1 became SEPTIN1 and the March family was renamed likewise. A naming authority changed a scientific nomenclature because a spreadsheet would not stop converting it.
What else is converted
Leading zeros disappear from anything that looks numeric. Long digit strings become scientific notation. Text containing a slash or a hyphen between numbers becomes a date, in an order decided by the locale. Anything beginning with an equals sign, a plus or a minus becomes a formula. Fractions become dates: three slash four is the third of April or the fourth of March depending on where you are.
None of these produces a warning, because each is the software doing what it was designed to do, correctly, for somebody else's data.
What travels and what does not
This is the part that catches organisations rather than individuals. A protection applied to your copy of the software is a setting on your machine, and a file is opened by other people on other machines with other settings and other locales.
So a column of identifiers that survives your workflow intact can be converted the first time a colleague opens the file to look at it and saves it. Nothing about the file records that this happened, and the person who did it has no reason to think they changed anything, because they did not type in a cell.
The only arrangement that survives contact with other people is to make the value unambiguous rather than to protect it: a leading apostrophe, an explicitly text-typed column in the source system, or a prefix that cannot be read as a number. All three are ugly and all three work.
Why it cannot be undone
The cell holds a date serial number or a floating-point value. The characters that were typed are gone, and no amount of reformatting brings them back: formatting a date cell as text shows the serial number, not the original string.
Which means the damage is done at the moment of entry or paste, and any remedy has to be in place before the data arrives rather than afterwards.
What actually works
Format the destination as text first, then paste. The column must be text before the data lands, not after.
Use the import dialogue rather than pasting, and set each column's type explicitly. Both products offer this, and it is the only route that lets you say what a column is before anything is interpreted.
Turn the conversion off where the product allows it. Excel added options in 2023 to disable specific automatic conversions, including the date one, at the application level. This is recent, is not present in older versions, and is per-installation rather than travelling with the file.
That last qualification is the important one: a file protected on your machine is unprotected on somebody else's, and this class of damage usually happens when a file is opened by a colleague.
What we cannot verify
The conversions are demonstrable in any copy of either product. The study of the genetics literature and the renaming by the naming authority are both published and can be read. The proportion of affected papers is that study's measurement of the corpus it examined and should not be generalised to other fields. The 2023 options are documented by the vendor; their availability differs by channel and version.
Where this bites hardest
Any workflow that exports from a system, opens the file to check it, and reimports. The opening is the damaging step, and it is the step everybody considers harmless because nothing was edited.
The check that finds it after the fact
Where a column should contain text and might contain converted values, one formula reports it: count how many cells in the column the sheet considers numeric. In a column of identifiers that answer should be zero, and any other answer names the problem immediately.
Putting that count in a cell at the top of the sheet costs nothing and turns an invisible conversion into a visible number, which is the whole method of the final part of this site.
In short
- The value is decided on arrival and the original text is not kept anywhere.
- Gene symbols became dates in a substantial share of published supplementary files.
- A naming authority renamed human genes in 2020 rather than keep fighting it.
- Leading zeros, long digits, slashes and a leading equals sign are all converted too.
- Nothing can be recovered afterwards, so the remedy must precede the data.
- The 2023 options to disable conversion are per-installation and do not travel with the file.