Double-clicking a CSV hands Excel a text file with no type information in it, so Excel guesses. It guesses well for most columns and badly for four of them, and it does not tell you which is which.
The fix is not a setting you turn on once. It is not opening the file — importing it instead, from the Data tab, where you get to say what each column is before a single value is read.
Short answer: it depends on how you open it
Same file, two routes, two different results.
A CSV is a plain text file. Every value in it is text, including the ones that look like numbers and dates. Excel has to decide what each one means, and the route you take decides whether you get a vote.
1Double-click, or File then Open
Excel applies General format to every column and converts whatever it can. No prompt, no wizard, no warning. Some of what it does here cannot be undone once you save.
2Data tab, then From Text/CSV
A preview window opens first. You set the column types there, and nothing is converted until you say so. Same file, same Excel, entirely different outcome.
Everything below assumes route 1, because that is the one everybody takes.
What survives and what does not
Five columns that go wrong, and how visible the damage is.
| In the file | On your screen | What happened |
|---|---|---|
00742 |
742 |
Read as a number, and numbers have no leading zeros. Hits ZIP codes and product codes hardest |
4532015112830366 |
4.53202E+15 |
Scientific notation, and the last digits are gone rather than hidden. See the next section |
03/04/2026 |
March 4 or April 3 | Read against your machine's regional setting, not the file's. A file that reads correctly on your laptop can read wrong on a colleague's |
MAR1 |
1-Mar | Text that resembles a date becomes one. Part codes and lab identifiers get caught by this |
Müller |
Müller |
The file is UTF-8 and Excel did not read it that way. Affects names, addresses, anything accented |
Rows one, four and five announce themselves. Row three is the dangerous one, because a wrong date still looks like a perfectly good date and nothing on screen suggests otherwise.
The one that is not recoverable
Widening the column fixes four of the five. Not this one.
Excel keeps 15 significant digits. A 16-digit card number loses its last digit, an 18-digit reference loses three, and the missing places come back as zeros. This is a storage limit rather than a display one, so the digits are not hiding behind the formatting — they are no longer in the workbook.
Careful — saving makes it permanent
While the file is still open, close it without saving and the original CSV on disk is untouched. Press Ctrl + S once, or save it as .xlsx, and the truncated version is what you have. Reformatting the column as Text afterwards changes nothing, because there is nothing left to reformat.
Try it now — check before you save
Put this beside a long-number column. If it returns fewer digits than the file has, the truncation already happened.
Close without saving, then import the file properly instead.
Import it instead
Four clicks, and it takes about as long as opening the file did.
Start from an empty workbook rather than the CSV. Data tab, then From Text/CSV, pick the file, and a preview window opens showing the first rows as Excel intends to read them.
Try it now — the setting that matters
In that preview, open the Data Type Detection list at the bottom and choose Do not detect data types. Every column arrives as text, exactly as written in the file. Then convert only the columns you actually calculate with.
Check the File Origin box in the same window while you are there. If accented characters look wrong in the preview, set it to UTF-8 and they correct themselves before anything is imported.
This route has a second benefit that shows up later. The import is remembered as a query, so when next month's file lands with the same name and shape, Refresh All reloads it with the same column types. The work is done once rather than every month.
If you already opened it
What can still be rescued, and in what order.
1Do not save. Close the file first
The CSV on disk still holds the original text. Everything is recoverable up to the moment you save over it, and nothing is guaranteed after.
2Import the same file the other way
Rather than repairing columns one at a time, throw the sheet away and bring the file in through Data, From Text/CSV. Repairing is slower and it cannot restore what was truncated.
3Only if the file is gone: pad the zeros back
Leading zeros can be reconstructed when you know the fixed width — a five-digit ZIP becomes =TEXT(A2,"00000"). Dates and truncated digits cannot be reconstructed this way, because the information needed to do it is what went missing.
Frequently asked
Checked August 2026.
Q.Can I make Excel stop converting, permanently?
A.There is no switch for it. Double-clicking a CSV always applies General format, on every machine and every version. Changing the habit is the only reliable fix, which is why the import route is worth the four clicks.
Q.Does quoting the values in the CSV protect them?
A.No. Quotes tell the parser where a field ends, not what type it is, so "00742" still arrives as 742. Whoever generates the file cannot solve this at their end.
Q.What about the old Text Import Wizard?
A.Still available, just hidden. File, Options, Data, then tick the legacy wizards; it appears under Data, Get Data, Legacy Wizards, From Text (Legacy). It does the same job. From Text/CSV is fewer clicks and remembers the import, so reach for the legacy one only if you already know its screens.
Q.My colleague opens the same file and gets different dates.
A.That is the regional setting, and it is working as designed. A day-first machine and a month-first machine read 03/04/2026 as two different days. If you share CSVs across regions, ask for ISO dates — 2026-04-03 reads the same everywhere.
Checklist
Work down this list
□ I did not double-click the CSV
□ Empty workbook, then Data, From Text/CSV
□ Data Type Detection set to Do not detect data types
□ File Origin checked if the data has accented characters
□ ID and code columns left as text on purpose
□ Spot-checked one long number and one date against the raw file
#ExcelCSV #LeadingZeros #CSVImport #ExcelDates #PowerQuery #ExcelTips
