Excel changed gene names to dates (SEPT1, MARCH1)

You open a gene list and some symbols are suddenly dates: SEPT2 reads 2-Sep, MARCH1 reads 1-Mar, DEC1 reads 1-Dec. This is a well-known problem in bioinformatics, and it is Excel doing its normal date guessing on text that happens to look like a month plus a number.

in the CSV: SEPT2, MARCH1, DEC1 in Excel: 2-Sep, 1-Mar, 1-Dec

Why it happens

Excel treats Sept2, Mar1 and similar patterns as day-and-month dates. It stores a date number and shows the result in a date format. The original letters are not kept. Several human gene families were named with these abbreviations (the SEPT, MARCH and DEC series), so any gene list can contain them. The nomenclature committee (HGNC) has since renamed many of them, to SEPTIN and MARCHF symbols among others, but older files and databases still use the old ones.

Prevent it

Never double-click the CSV. Import it:

1. Open a blank workbook.
2. Choose Data ▸ From Text/CSV and pick the file.
3. Set Data Type Detection to Do not detect data types, or click the gene column header in the preview and set its type to Text.
4. Load.

If you type or paste gene names directly into a sheet, format the column as Text first (select it, Ctrl+1, Text), before pasting. Formatting after the fact does nothing: the damage is done on entry.

In Google Sheets, import with the option "Convert text to numbers, dates, and formulas" turned off, and format the column as Plain text before pasting.

Repair what is already converted

Converted cells are real dates now, so you can rebuild the symbols. If you know the affected genes belong to the SEPT, MARCH and DEC families, a formula in a helper column does it:

=IF(ISNUMBER(A2), IF(MONTH(A2)=9,"SEPT",IF(MONTH(A2)=3,"MARCH","DEC")) & DAY(A2), A2)

That only works if those three families are the only ones affected in your data. Check a few results against the source. If the cells show plain numbers like 44805, format them as a date first so MONTH and DAY can read them. Then copy the helper column and paste as values over the original. If in doubt, go back to the original source file instead of trusting the rebuilt names.

What CSV Fixer does here

It doesn't repair gene names that Excel has already converted. If your original CSV still contains the text SEPT2, CSV Fixer reads it as raw text and keeps it as SEPT2, because it never turns text into dates. Its date option only touches columns that are made of numeric dates, and only if you switch it on. But once you open the result by double-clicking, Excel will convert again, so use the import route above.

Check files you receive from others

The mistake spreads when a spreadsheet passes between people. If someone opens a gene list by double-clicking and saves it, the symbols are changed for everyone downstream. Before using a supplementary table from a paper or a collaborator, scan the gene column for entries that look like dates (1-Sep, 2-Mar, 1-Dec) or for numbers in the 40000s. Sort the column: dates cluster together and are easy to spot. When you share your own lists, send them as CSV or TSV with a note, or import them to text before sending.

← Back to the CSV Fixer tool