Excel Turned Gene Names into Dates? Find and Fix Them
Paste a whole table or drop a CSV / TSV to find gene names that Excel converted into dates (SEPT1 → 1-Sep, MARCH1 → 1-Mar, OCT4 → 4-Oct…), restore them to current official symbols and download the fixed table.
Release to load this file
1. Table
2. Options
3. Findings
How to stop this from happening again
Double-clicking a CSV or pasting straight into Excel converts gene names automatically. Current symbols such as SEPTIN1 and MARCHF1 are safe, but old names like SEPT1 and OCT4 are not. Import and save gene tables as text:
- Importing a CSV / TSV in Excel: Data → From Text/CSV, pick the file, set “Data Type Detection” to “Do not detect data types” in the preview, then Load — every column comes in as text.
- Legacy Text Import Wizard (Data → From Text): in step 3 select the gene column and set “Column data format” to “Text”, then Finish.
- Before pasting: select the target column → right-click → Format Cells → Text, then paste.
- Microsoft 365 (versions from late 2023): File → Options → Data → Automatic Data Conversion, untick “Convert continuous letters and numbers to a date” and the option that converts digits around the letter E to scientific notation.
- In R / Python: readr, data.table and pandas do not do this when reading CSV; share result tables as TSV / CSV, or set the gene column to text in the xlsx.
- Why this happens: Excel reads gene names such as SEPT1, MARCH1, DEC1 and OCT4 as dates, and RIKEN clone names such as 2310009E13 as the number 2.31E+19. A 2016 survey found these errors in about one in five papers with supplementary Excel gene lists (Ziemann et al., Genome Biology 2016; first reported by Zeeberg et al., BMC Bioinformatics 2004).
- Recognised forms: 1-Sep, Sep-1, 01-Sep, 1-Sep-24, 1-Sep-2024, Sep 1, 2024, 2024/9/1, 2024-09-01, 9/1/2024, 1/9/2024, 01.09.2024, 9月1日, 2024年9月1日, plus common French, German, Spanish, Italian and Portuguese month abbreviations.
- Only March, September, October and December (the MARCH / SEPT / OCT / DEC families) can be genes, and the day must fall within that family’s numbering, which is computed from the gene database (e.g. human SEPT up to 14, MARCH up to 11). For all-numeric forms like 9/1/2024, if both month/day and day/month readings match a gene (3/9/2024 could be MARCH9 or SEPT3) the cell is marked ambiguous with both candidates; if the table contains a date like 9/14/2024 that can only be read one way, that order is used throughout.
- Column rules: a column where at least half the cells are dates and fewer than one in ten are gene names is a real date column and is left alone (unless its header is gene / symbol or similar, or every date in it maps to a gene and the column also has ordinary gene names). A column where at least half the cells are gene names (counting restorable dates) and that has ordinary gene names or a gene header is a gene column; its restorable cells are ticked by default. A column with no ordinary gene name that only has an ID / name header, a single pasted column, or a gene-headed column containing dates that match no gene is listed as a possible gene column but not ticked. Date-like gene names in other columns are listed but not ticked. Ambiguous cells are not ticked either; picking a candidate ticks them, and “select all” only ticks confident ones. Leading # comment lines are skipped (UCSC-style “#name…” headers are still recognised); whether the first row is a header is detected automatically and can be changed under “2. Options”.
- Restored to current official symbols: SEPT1 → SEPTIN1, MARCH1 → MARCHF1, DEC1 → DELEC1 (DEC1 is also an alias of BHLHE40; switch it in the drop-down). Species is detected from letter case (human all caps, mouse capitalised) or can be chosen; mouse output uses mouse case, e.g. Septin1, Marchf1. Names not found in the gene database are restored to the old name and marked “unverified”.
- Once a RIKEN clone name has become 2.31E+19 its middle digits are gone; the tool can only point to the cell, not restore it.
- The “original format” download changes only the cells you tick; everything else is written back byte for byte (delimiters, quotes, line breaks), ready for R / Python. The CSV download is meant for opening in Excel: cells that start with = + - @ and are not numbers (e.g. -log10(padj), +/-) get a leading apostrophe so they are not run as formulas; negative numbers and a lone + / - are untouched. GBK-encoded files saved by Chinese Excel are read correctly and downloaded as UTF-8.