How to Stop Excel from Turning IDs into Dates

You type 1-2, 3/4, or 2024-01 into a cell and Excel shows 2-Jan, 4-Mar, or Jan-24. Excel guessed the entry was a date and stored it as one. These settings stop the guess.

Format the column as Text before typing

Select the column, go to Home, open the Number Format dropdown (it reads General), and choose Text. Anything typed into those cells now stays exactly as entered. Do this before typing; changing the format afterward does not undo a conversion that already happened.

Prefix a single entry with an apostrophe

Type '1-2 and press Enter. The apostrophe tells Excel to store the text as typed, and it does not show in the cell or when printed.

Set the column type when importing a file

Go to Data, From Text/CSV, choose the file, and in the preview click Transform Data. In Power Query, click the type icon at the left of the column header and choose Text, then Close & Load. Setting the type at import protects every row of a large ID column at once.

Turn off automatic conversion in Microsoft 365

Go to File, Options, Data. Under Automatic Data Conversion, untick the conversions you do not want: removing leading zeros, truncating long numbers to scientific notation, converting digits around the letter E, and converting continuous letters and numbers such as MAR1 to a date. These options do not cover entries like 1-2 or 3/4, which still need the Text format from the first step.

Re-enter cells that already converted

Once Excel has stored 1-2 as a date, the original text is gone; the cell holds a date serial number. Changing the format to Text now shows that number (for example 46024), not 1-2. Set the format to Text, then retype the values, or re-import the file with the column typed as Text.

If you use Google Sheets. Select the column and go to Format, Number, Plain text before typing. Sheets otherwise makes the same guesses, and gene names and part numbers such as MAR1, SEPT2, or DEC1 are the classic victims in both programs.