Why Excel Changes Numbers and Dates When You Open a CSV

By Andrew Butson · July 2, 2026 · 7 min read

A product code that was 00214 in the spreadsheet you were sent is 214 the moment you open it in Excel. A column of dates written as 3/4 has turned into 04-Mar-24 or 03-Apr-24 depending on whose computer opened it. A sixteen-digit account number now reads 1.23457E+15. Nothing about the original file was wrong; the CSV held exactly what it was supposed to. The damage happens the instant Excel opens it.

This isn't a bug you've stumbled onto; it's Excel's default behaviour every time you double-click a .csv, and it's been the same for decades. Understanding why it happens is the difference between it costing you an afternoon of VLOOKUP mismatches and it costing you nothing.

Why it happens: a CSV has no idea what a "number" is

A CSV file is plain text. Every value, whether a date, an ID, or a price, is just a string of characters separated by commas; nothing in the file says "this column is text" or "this column is a date." When you double-click a .csv, Windows hands it to Excel using the same code path Excel uses for opening any text file, and Excel's importer guesses each column's type from its content and your machine's regional settings, then applies that guess as real formatting: not just a different display, but a changed underlying value.

That guessing is where each specific symptom comes from. A digit string with no other characters gets read as a number, and numbers don't carry leading zeros, so 00214 becomes 214. Anything that looks date-shaped gets parsed as a date using your Windows short-date format, which is why the same file reads as a different date on a US machine (month first) than on a UK one (day first): the file didn't change, the guess did. And Excel stores every number as a 64-bit floating-point value with about 15 significant digits of precision; a 16- or 20-digit account number, barcode or database ID exceeds that, so Excel both rounds it and switches the display to scientific notation. Crucially, the digits beyond the 15th aren't hidden, they're gone. If you save the file back out from Excel afterwards, that data doesn't come back.

The fix that actually prevents it: import, don't open

Double-clicking is what causes the problem. Using Excel's import tool instead lets you tell it, column by column, what each one actually is before a single guess gets applied.

• Current Excel (Microsoft 365, Excel 2021+): Data tab → "From Text/CSV" → select the file. In the preview window, choose "Transform Data" to open Power Query, click the column you need to protect, and set its Data Type (top-left of the column) to "Text" before loading. This is the single most reliable fix, and it works the same way every time.
• Older Excel (2010–2016): Data tab → "From Text," which opens the three-step Text Import Wizard. On the final step, click each column that holds IDs, codes or dates you don't want reinterpreted, and set "Column data format" to "Text."
• One-off fix for a single cell: type an apostrophe before the value, e.g. '00214; Excel stores it as text and displays it exactly as typed, but this only helps values you're entering by hand, not a whole imported column.
• Format the column as Text before you paste or type into it. Formatting a column after Excel has already converted the values won't restore what's already been stripped or rounded off.

Where converting the file first actually helps

The reason the import wizard works is that it lets you set a type per column before Excel touches the data; converting your CSV into a real .xlsx workbook first does the same thing structurally, which is where Morphr's CSV-to-XLSX conversion fits. It writes each cell explicitly: a value only becomes a numeric cell if it's a clean integer or decimal within a range Excel can store exactly, and everything else, including anything with leading zeros or more digits than Excel's numeric precision can hold, is written as an explicit text cell. Opening that .xlsx afterwards doesn't run Excel's guess-based CSV importer at all, because there's no guessing left to do; the type is already decided in the file.

It isn't a fix for data that's already been mangled, though. If the CSV you have has already been opened and re-saved by Excel once, the leading zeros and truncated digits are genuinely gone, not just hidden by formatting. The fix has to happen on the way in, either through the import wizard or by converting to a properly typed spreadsheet before anyone opens it casually.

Frequently asked questions

Why does the same CSV show different dates on my coworker's computer?

Excel parses date-shaped values using your Windows region's short-date format, not a format stored in the file. A US-configured PC reads 03/04/2024 as March 4th; a UK-configured one reads the same text as April 3rd. The file never changes; only the guess does, machine to machine.

Can I get my leading zeros back after Excel strips them?

Only if you still have the original, unaltered CSV. If Excel has already re-saved the file, the zeros are gone from the data itself, not just hidden, so re-typing the values into a column pre-formatted as Text is the only way forward from there.

Does converting to XLSX also fix the scientific-notation problem?

Yes, for the same reason it fixes leading zeros: Morphr only writes a value as a number when it's a clean integer or decimal Excel can store exactly. A 16-digit account number or barcode falls outside that range and is written as text instead, so it opens as the full digit string rather than getting rounded into 1.23457E+15.

Which columns are actually at risk?

Anything that's a string of digits without being a value you'll do arithmetic on: postal or zip codes, phone numbers, SKUs and product codes, invoice or account numbers, and any date column you want to control the format of yourself. Genuine quantities and prices are usually fine being read as numbers.