How to stop Excel turning numbers into dates and dropping leading zeros

Excel changes CSV values because it guesses each cell’s type when opening the file: 1-2 looks like a date, 00042 like the number 42, and a 16-digit ID like a huge number. Import the file with Data → From Text/CSV and set those columns to Text before loading. Double-clicking a CSV gives you no chance to do this.

What Excel changes, and what you lose

• Dates from codes: part numbers like 1-2, 3/4 or MAR1 become dates. Gene names and product codes are famous victims. Once converted, the original text is gone from that cell.
• Leading zeros: postcodes, phone numbers and IDs such as 00042 become 42.
• Long numbers: card-style or account numbers longer than 15 digits are shown as 1.23457E+15, and digits after the fifteenth are replaced with zeros, because Excel stores numbers with 15 significant digits. That damage cannot be undone by reformatting.
• Fractions and ratios: 1/2 can become a date; a score like 3-1 too.
• Decimal marks: in comma-decimal regions, 1.5 may be read as a date or as text.

Reformatting a damaged cell as Text afterwards shows the converted value, not the original. The fix has to happen on the way in.

Import with the columns set to Text

1. In a blank workbook, go to Data → From Text/CSV and choose the file.
2. Check the delimiter, then click Transform Data to open Power Query.
3. Power Query adds a “Changed Type” step automatically. Delete that step, or select the sensitive columns and set Data Type to Text.
4. Close & Load. The values arrive exactly as written.

In older Excel versions, the legacy Text Import Wizard (Data → Get External Data → From Text, or enabled under Options → Data) lets you mark each column as Text in step 3.

Recent Excel versions: turn off automatic conversion

Newer versions of Excel for Microsoft 365 have options under File → Options → Data → Automatic Data Conversion to stop removing leading zeros, stop converting long numbers to scientific notation, and stop converting text to dates. If your version has them, switching them off protects files you open by double-clicking too. Check whether your version includes these settings.

When you create the CSV

• Keep a source of truth that is not the spreadsheet. Generate CSVs from the database or JSON, and treat Excel copies as views. JSON to CSV converts records without changing values.
• Prefer .xlsx for people who will use Excel. A workbook stores types explicitly, so a text cell stays text. Tables in PDFs can go straight to .xlsx with the PDF Table Extractor.
• Use unambiguous formats for real dates: 2026-10-20 (ISO 8601) is read the same way everywhere, unlike 10/11/2026.
• Avoid tricks like adding an apostrophe or ="00042" to the CSV. They work in Excel but leave junk characters for every other program that reads the file.

Google Sheets does it too

Google Sheets also converts values on import unless you untick “Convert text to numbers, dates and formulas” in the File → Import dialog. LibreOffice Calc’s import dialog lets you set column types the same way as Excel’s. Whatever program you use, look for the import screen rather than opening the file directly.

Recovering a file that was saved over

If someone opened a CSV, Excel converted it, and it was saved, the converted values are now in the file. Leading zeros can sometimes be restored if every value had a fixed length (format the column as 00000), but dates from codes and truncated long numbers cannot be reconstructed. Go back to the original export.

Mistakes to avoid

• Double-clicking CSVs that contain IDs, codes or phone numbers.
• Saving a CSV from Excel and sending it back into a system as if nothing changed.
• Storing phone numbers or IDs as numbers anywhere; they are text that happens to be digits.

Checklist

• Imported via Data → From Text/CSV?
• ID, code, postcode and phone columns set to Text?
• Original export kept untouched?