Excel Removes Leading Zeros

The Short Answer

A CSV has no column types, so Excel guesses. If a value looks like a number, Excel stores it as a number. Numbers have no leading zeros, so ZIP code 02134 becomes 2134. Numbers longer than 15 digits lose their last digits.

Saving the column as text fixes it. The most reliable way is to turn the CSV into an .xlsx with those columns stored as text, because then it opens correctly on a double-click, for anyone.

What Excel Does to Number-Like Text

Microsoft explains it in Keeping leading zeros and large numbers: Excel removes leading zeros and shows large numbers in scientific notation so that formulas and maths work on them. It also says Excel has "a maximum precision of 15 significant digits", so in a number with 16 or more digits, every digit after the 15th becomes a zero.

The last one is the dangerous one: the ID looks fine at a glance in scientific notation, but the real digits are gone. If you save the file, the changed values are written back.

Fix 1: Save an .xlsx With Text Columns

1. Open the CSV Fixer and pick your CSV.
2. Under Keep as text, check the ticked columns. Columns with leading zeros, a leading + or more than 15 digits are ticked for you; tick any others you want kept exactly.
3. Check the preview, then click Download .xlsx.
4. Double-click the .xlsx. The ticked columns are text cells, so 02134 and the full 18-digit ID are shown exactly as written.

We checked the output by reading it back with a separate spreadsheet library: the ZIP code and an 18-digit ID are stored as text cells with the Text number format, while ordinary numbers in other columns stay numbers.

Fix 2: Import the CSV as Text in Excel

Instead of double-clicking the CSV, import it, which lets you set each column's type. Microsoft's steps:

1. Go to Data → From Text/CSV and pick the file.
2. Click Transform Data (called Edit in some versions) to open Power Query.
3. Select the column, then set Data Type to Text and choose Replace current.
4. Click Close & Load.

In the same import window, the data type detection drop-down also has an option to not detect data types, which leaves every column as text (Microsoft's Text/CSV connector documentation).

Fix 3: Turn Off the Conversion (Newer Excel Only)

Excel for Microsoft 365 and Excel 2024, on Windows and Mac, have an Automatic Data Conversion setting:

• Windows: File → Options → Data → Automatic Data Conversion.
• Mac: Excel → Preferences → Edit → Automatic Data Conversion.

Untick Removing leading zeros from numerical text and converting to a number and Truncating numerical data to 15 digits of precision. This only changes your own Excel; anyone you send the CSV to still gets the conversion. Microsoft lists the setting only for Microsoft 365 and Excel 2024, so older versions such as Excel 2016 and 2019 don't offer it.

Things That Trip People Up

• Formatting afterwards doesn't help. Once a cell holds 2134, formatting it as Text shows "2134". Re-import the original instead.
• Saving the CSV from Excel makes it permanent. Excel writes the converted values back, so the zeros are gone from the file too. Keep the original export.
• Phone numbers lose the +. A value like +441234567890 can be read as a positive number, and the plus sign is then dropped. Treat phone numbers as text.
• Typing a value by hand. Put an apostrophe in front ('02134) and Excel keeps it as text, as Microsoft describes. That's fine for a few cells, not for a whole export.
• Other columns change too. Excel can also turn codes into dates. In the newer Excel versions above, that has its own Automatic Data Conversion option.

Platform Notes

Excel for Windows has all three fixes. Excel for Mac has Data → Get Data → From Text/CSV in Microsoft 365 and Excel 2024, and the Automatic Data Conversion setting under Preferences. The .xlsx route works in any version of Excel that opens .xlsx files, because the cells are stored as text in the file itself.