How to Stop Excel Turning Your Codes Into Dates

Format the cells as Text before the data goes in. That's the only reliable fix, because Excel converts as you type and the original text is not kept anywhere.

If it has already happened, the section on recovery near the end is honest about what can and can't be got back. Mostly: can't.

The same eight entries, twice. The only difference is that the right-hand column was formatted as Text before anything was typed into it.

What's actually happening

This is not a display problem, and that's the part people get wrong.

When you type 1-2 into a normal cell, Excel decides it's a date, converts it to the number 46024, and stores that. The characters "1-2" are discarded at the moment you press Enter.

So reformatting the column as Text afterwards doesn't bring the code back — it shows you 46024, which is what the file now contains. Every "just change the format" answer online is answering a different question.

You type	Excel shows	Excel stores
1-2	2-Jan	46024
5-10	10-May	46152
3/4	4-Mar	46085
SEPT1	1-Sep	46266
1E5	100000	100000
00123	123	123

Note the last two. Not everything becomes a date — 1E5 is read as scientific notation, and leading zeros are simply dropped. Same mechanism, same permanence.

The famous version of this problem

Gene names. SEPT1, MARCH1 and DEC1 are real human gene symbols, and Excel turns all three into dates.

A 2016 study in Genome Biology examined supplementary spreadsheets published with genomics papers and found that roughly a fifth of them contained gene names corrupted this way. Not obscure journals — mainstream published research.

It got bad enough that in 2020 the body responsible for human gene naming renamed the affected genes, so that SEPT1 became SEPTIN1. The scientific community changed its nomenclature because a spreadsheet wouldn't stop guessing.

Worth knowing for two reasons: it's the clearest evidence that this is a real data-integrity problem rather than an annoyance, and it tells you nobody is going to fix it for you.

Prevention, in order of how much you'll use them

1. Format the column as Text first

Select the column, Home → Number Format → Text, then enter the data. Everything typed after that stays exactly as typed.

⚠️ The order is the whole thing. Format then type works. Type then format loses the data.

The trade-off: nothing in a Text column will ever calculate. That's correct for a column of codes — you don't add up part numbers — but don't apply it to a column you'll need to total later.

2. A leading apostrophe, for one-offs

'1-2

The apostrophe tells Excel "treat this as text" and isn't stored as part of the value or shown in the cell. Copy the cell elsewhere and you get 1-2, not '1-2.

Perfect for a handful of cells. Impractical for a column of four hundred.

3. Import properly — never double-click a CSV

This is where most corruption actually happens, and it happens in bulk.

A CSV file is plain text. Every value in it is a string until something interprets it. Double-click it and Excel interprets the lot at once, with no chance to intervene.

Instead:

1. Open Excel first, with a blank workbook.
2. Data → Get Data → From Text/CSV.
3. Pick the file. A preview appears.
4. Click Transform Data — not Load.
5. In the editor, click each column that holds codes and set its type to Text.
6. Close & Load.

Step 4 is the one that matters. Load applies Excel's guesses; Transform Data lets you overrule them before anything is converted.

The same route handles the file next month — the column types are remembered, so a refresh re-imports correctly without asking again.

4. Check what your version lets you turn off

Recent versions of Excel 365 added settings under File → Options → Data, in a section about automatic data conversion, covering things like removing leading zeros and converting long digit strings to scientific notation.

Worth looking at if it's there — the options vary by version and they don't cover every case, so treat them as a helpful extra rather than a replacement for importing properly.

What you can and can't get back

If the conversion has already happened, whether you can recover depends entirely on whether the original was reconstructible from the number.

Damage	Recoverable?	How
Leading zeros dropped	Yes, if you know the width	=TEXT(A2,"00000")
1-2 became a date	Usually	=TEXT(A2,"d-m")
SEPT1 became a date	Yes, but by hand	You know what it was; retype it
1E5 became 100000	No	Could have been either. Nothing distinguishes them.
16-digit number rounded	No	The lost digits aren't in the file

For the date cases, check the result against a source you trust before accepting it. =TEXT(A2,"d-m") gives 2-1 where the original may have been 1-2 — the day and month order depends on how it was read in the first place, and getting it backwards produces plausible, wrong data.

The long-number case is covered further in the duplicates guide, where the same 15-digit limit causes different account numbers to be flagged as identical.

If the original file still exists, re-import it properly. That is always better than reconstructing.

Does this work in Google Sheets?

Sheets does the same thing and gives you a cleaner way to stop it: Format → Number → Plain text on the column before entry, and the leading apostrophe works identically.

Sheets is also better on import. File → Import offers a "Convert text to numbers, dates, and formulas" option — untick it and nothing is interpreted at all.

That single checkbox is the thing Excel is missing.

When it goes wrong

I formatted as Text and it still converted

The data was already in the cells. Formatting doesn't re-read anything — it changes how the existing value displays. Clear the cells, format, then re-enter.

The apostrophe is showing in the cell

Two apostrophes were typed, or the cell was already formatted as Text so the apostrophe became part of the value. Only one is needed, and only on a General cell.

Pasting into a Text column still converts

Paste carries its own formatting. Use Paste Special → Values, or better, use Get Data rather than pasting.

The codes are fine on my machine and wrong on a colleague's

Different regional settings reading day-month order differently. If the file has been through a CSV round trip, the conversion happened at import and now differs between you. Only proper import prevents this.

Numbers are stored as text and won't add up

The opposite problem, and a genuine trade-off of Text formatting. Only format the columns that hold codes. Converting text back to numbers is covered here.

Common questions

Can I turn date recognition off completely?

Not for typed entry. There's no setting for it, and there never has been. Formatting as Text beforehand is the closest thing.

Why does Excel do this at all?

Because for most people, most of the time, typing 3/4 and getting a date is what was wanted. It's a good default that is catastrophic for the minority whose data looks like dates and isn't.

How do I check whether existing data was mangled?

=ISNUMBER(A2) down a column that should be codes. Any TRUE is a converted cell. Any code column with dates in it should also be visibly right-aligned where the others sit left.

What if I need both codes and calculations in one column?

You don't. Split them — a Text column for the identifier, a number column for the quantity. A column doing two jobs is the root of this whole problem.

Is there a safe way to name codes so this never happens?

Yes, and it's worth doing if you control the format: put a letter prefix on everything. REF-1-2 is never a date. Any consistent non-numeric prefix removes the ambiguity entirely.

The short version

• Excel converts on entry and discards the original. It isn't a formatting issue.
• Reformatting afterwards shows the serial number, not your code.
• Format as Text before the data goes in. Order is everything.
• A leading apostrophe handles one-off cells and isn't stored.
• Never double-click a CSV. Get Data → From Text/CSV → Transform Data, set the column to Text.
• Dates and leading zeros are often reconstructible; scientific notation and truncated long numbers are not.
• A letter prefix on your codes prevents the whole class of problem.

Next steps: if the file is already through the mill, the cleaning sequence starts by finding what got converted, and a code that became a date is one reason two lists refuse to match. The same 15-digit limit that truncates long numbers also invents duplicates that aren't there.