Stopping Excel From Turning Gene Names and IDs Into Dates

Open a list of human gene symbols in Excel and a few of them quietly turn into dates. SEPT2 becomes 2-Sep, MARCH1 becomes 1-Mar, DEC1 becomes 1-Dec. Nothing warns you, the cell looks tidy, and the original text is gone, replaced by a serial number that merely displays like a date. Researchers have been bitten by this for a long time, and the same mechanism hits any identifier that looks like a month and a number: product codes, plate positions, part numbers, section labels. This is one case of the wider problem in opening CSV files in Excel and Sheets without damage, and it is the one with the most expensive consequences, because the damage is easy to miss and impossible to undo from inside the cell.

What Excel does to the cell

When Excel reads a CSV by opening it, it looks at each cell and asks whether the text could be a number, a date or a boolean. For SEPT2 the answer is yes: SEPT is accepted as an abbreviation of September, and 2 can be a day. Excel stores the date as a count of days (a serial number such as 46267 for 2 September 2026, with the year filled in on its own) and applies a display format of d-mmm. The cell shows 2-Sep. The characters S, E, P, T are not stored anywhere.

That is why this is worse than a display problem. Changing the cell's format back to General shows the serial number, and changing it to Text shows the same number as text. The year is also invented, which means two different files opened in two different years would get two different serials for the same gene. Saving the sheet as CSV writes the displayed text, 2-Sep, or the serial, depending on the export route, and in neither case does the symbol come back.

The collision is not random. It needs a three or four letter month abbreviation followed by one or two digits, or the reverse, so it hits a specific family. Among human genes the best known are the SEPT family (septins), the MARCH family (membrane-associated ring-CH-type proteins) and DEC1. The nomenclature committee eventually gave in and renamed them: septins are now SEPTIN2, the MARCH family MARCHF1, and DEC1 is DELEC1, which is the clearest admission anywhere that a spreadsheet changed how a field names things. Older papers, older annotation files and older databases still carry the old symbols, which is why the problem has not gone away.

How common is it? A 2016 analysis in Genome Biology looked at gene lists in the supplementary files of papers from 18 journals and found that roughly one in five papers contained at least one mangled gene name (Ziemann, Eren and El-Osta, 2016). Those are people who check their work, publishing in journals with reviewers. A business spreadsheet full of part numbers has no reviewer at all.

Find the values at risk before anyone opens the file

The cheapest defense is to test the file for identifiers that Excel will convert, before it goes to a person with a mouse. The pattern is simple: a month name or abbreviation followed by an optional separator and one or two digits, or the digits first. Here it is as a small script that reads one column of a CSV and prints every offending value with its line number:

import csv, re, sys

MONTHS = "jan feb mar apr may jun jul aug sep sept oct nov dec".split()
PREFIX = re.compile(r"^(%s)[a-z]*[-/ ]?\d{1,2}$" % "|".join(MONTHS), re.I)
SUFFIX = re.compile(r"^\d{1,2}[-/ ]?(%s)[a-z]*$" % "|".join(MONTHS), re.I)

def risky(value):
    return bool(PREFIX.match(value) or SUFFIX.match(value))

path, column = sys.argv[1], sys.argv[2]
with open(path, newline="", encoding="utf-8") as f:
    for line_no, row in enumerate(csv.DictReader(f), start=2):
        if risky(row[column]):
            print(f"line {line_no}: {row[column]}")

Feed it a file with a few genes that are safe and a few that are not:

sample,gene,count
s1,SEPT2,14
s1,MARCH1,9
s1,DEC1,3
s1,BRCA1,120
s2,OCT4,41
s2,1-Mar,5
s2,SEPT10,2
s2,TP53,200

python3 scan.py genes.csv gene

line 2: SEPT2
line 3: MARCH1
line 4: DEC1
line 6: OCT4
line 7: 1-Mar
line 8: SEPT10

BRCA1 and TP53 pass, because BRC and TP are not months. The script is deliberately a little too eager: MAR followed by any letters and a digit matches, so MARCHF1 would be flagged too, even though Excel leaves that one alone. A false alarm costs a second of reading; a miss costs a corrupted dataset. Run it on every column that holds identifiers, and put it in the checks you run on incoming files (writing data quality checks for a CSV).

Stop the conversion on the way in

Three routes keep a spreadsheet from guessing, and they all amount to telling it that the column is text.

Excel, import route. Use Data > From Text/CSV, pick the file, open the data-type detection dropdown and choose Do not detect data types. Everything loads as text. Click Transform Data if you want to type some columns as numbers, and leave the gene column as Text. The query remembers the types, so a refresh with a new file applies them again, which is the right setup for a weekly list (importing a CSV into Excel with Power Query).

Excel, paste route. If the gene list comes from somewhere else and you paste it, select the destination column first and set its format to Text under Home > Number format. Pasting into a General column converts on arrival, and formatting afterwards is too late. For a single cell, a leading apostrophe ('SEPT2) stores text; the apostrophe is not part of the value.

Google Sheets. Use File > Import, and untick Convert text to numbers, dates, and formulas. Pasting is the usual leak in Sheets: use Edit > Paste special > Values only into a column you have set to Format > Number > Plain text.

LibreOffice Calc. Opening a .csv shows the Text Import dialog. Click the gene column in the preview and set the Column type to Text, and make sure Detect special numbers is off.

Double-clicking the file in Explorer or Finder uses none of this, and that is how most of the damage happens. For a team that shares gene lists, the habit to teach is "never double-click a CSV" rather than "check your cells afterwards".

When it has already happened

If the file was saved after conversion, the symbol is not in the file. The question is whether it can be reconstructed, and that depends entirely on how many candidates there are.

For gene symbols the candidate set is small and public. Build a lookup table of the old symbols that collide, SEPT1 to SEPT14, MARCH1 to MARCH11, DEC1, and so on, keyed by the day and month they display as. A converted cell showing 2-Sep can come back as SEPT2, and 1-Mar as MARCH1. This works because the mapping is one to one for that vocabulary, and it fails whenever a real date could be in the same column or a symbol outside the list was also converted. Do it in a query so it is repeatable:

SELECT COALESCE(m.symbol, d.value) AS gene
FROM damaged d
LEFT JOIN date_collisions m ON m.shown = d.value;

Check the result against the source of the list, not against your memory. If the original file exists, go back to it: it is always the better route, and the right repair for a file that matters is to re-export from the source and import it correctly.

For identifiers outside a known vocabulary, such as part numbers like JUN12 or 10-12, reconstruction is guessing. 10-12 can be 10 December or 12 October depending on the locale of the machine that opened it, and the stored serial no longer says which. Treat those columns as lost and ask the producer for the file again.

Make the pipeline survive Excel

Defenses that depend on every person remembering are weak. Better ones move the risk out of the path:

• Deliver gene lists with a stable identifier next to the symbol, such as the Ensembl or HGNC ID, so that a mangled symbol can be repaired from a column that cannot be mistaken for a date. ENSG00000184702 converts to nothing, because no month begins that way.

• Ship a data dictionary that says the column is an identifier and must be handled as text (writing a schema for a CSV).

• Run the scan above in the intake step, and fail the file when it finds a symbol that is also a date pattern and the column is declared text, with a message that names the line.

• Where a spreadsheet is unavoidable, hand over an .xlsx with the column already typed as text, and keep the CSV as the machine copy.

The same defenses cover other identifier shapes. Lab sample codes like 3-4, well positions that run A1 through H12, scientific names with a number, and locations such as May-12 all hit the same parsers. The fix is the same each time: decide that the column is text, say so in the import, and test for the pattern.

When renaming is the better fix

If you control the data, change the identifier instead of defending it forever. The nomenclature committee did exactly that for human genes, and a new internal code scheme should avoid a shape that a spreadsheet reads as a date. Add a prefix that is not a month (G-SEPT2), or use a numeric surrogate key for joins and keep the symbol as a label.

Renaming is the wrong answer when you must interoperate with data you do not control. Public databases and older publications keep their symbols, and your lookups must match what they say. In that case keep the symbol exactly, treat it as text everywhere, and add the scan to the intake step so that the first Excel session does not undo the work. If the data is going into a database next, load the column as text and compare it against the reference table before anything else, as in validating a CSV against a lookup table. Microsoft documents the import route in import or export text files, and the long-number version of this problem is in keeping long numbers intact when Excel opens a CSV.