How to Keep Leading Zeros When Opening CSV in Excel

Why Excel removes leading zeros from CSV

A CSV stores characters and separators, with no spreadsheet cell types or display formats. When Excel opens it directly, it can interpret digit-only fields as numbers. Numeric 123 has no record of the two leading zeros in the identifier 00123. Opening a CSV does not necessarily change the source file; saving over it after conversion can.

CSV quotation marks group fields; they do not reliably tell Excel to treat a value as text. Even "00123" can be interpreted as a number.

These are labels, even when they contain only digits. Arithmetic is usually inappropriate for postal codes or account references.

Option 1: import selected columns as text in Excel

Desktop Excel with Power Query

Use this route in Windows Excel versions with Data → From Text/CSV, including Microsoft 365 and Excel 2016–2024. Labels and availability differ across Mac versions and update channels; use the equivalent text-import option when available.

1. Open a blank workbook. Choose Data → From Text/CSV (sometimes under Get Data → From File) and select the original CSV.
2. Check the encoding and delimiter in the preview. Choose Transform Data or Edit, rather than loading immediately.
3. In the query editor, inspect Applied Steps. If an automatic Changed Type step has already converted identifiers to numbers or dates, remove that step before setting their types. Converting an already-altered number back to text will not recover its original digits.
4. Select each identifier column and set Data Type → Text. If prompted, replace the existing type conversion. Confirm the preview still shows the original zeros and long identifiers.
5. Choose Close & Load. Verify the cells, then save the workbook as .xlsx.

See Microsoft’s guidance on leading zeros and importing columns as text.

Excel with the legacy Text Import Wizard

In older desktop installations, use Data → From Text. In current Windows Excel, enable From Text (Legacy) under File → Options → Data → Show legacy data import wizards, then open it through Data → Get Data → Legacy Wizards.

1. Select the CSV and choose Delimited in step 1, with the appropriate file encoding.
2. In step 2, select the delimiter and quotation-mark text qualifier. Leave Treat consecutive delimiters as one off when adjacent separators represent empty fields.
3. In step 3, select each identifier column in the preview and choose Text under Column data format.
4. Finish the import, inspect the results, and save as XLSX.

For the exact controls, see Microsoft’s Text Import Wizard instructions. Excel for the web and mobile apps may not offer these desktop dialogs; create a text-cell XLSX first if you cannot control import types.

Automatic Data Conversions settings

Microsoft 365 and Excel 2024 also offer settings for automatic conversions, including leading zeros, long numbers and date-like text. Availability and menu locations vary by platform. These settings are preventive; they do not restore values already converted. See Microsoft’s automatic data conversion options, and still verify your imported values.

Option 2: convert CSV to XLSX with text cells

1. Open the CSV Rescue CSV-to-Excel converter.
2. Drop your original UTF-8 CSV, TSV or TXT file, or select Choose CSV file. To explore first, choose Try sample CSV.
3. Check Delimiter. Auto-detect supports comma, semicolon, tab and pipe; choose one explicitly if needed.
4. Select Convert to XLSX. Review Preview text values, then choose Download Excel (.xlsx).
5. Open the downloaded workbook and check your identifier cells before using it.

The converter writes every field, including the first row, as a string cell in one worksheet called CSV data. XLSX records cell types, so Excel can read 00123 as text without guessing whether it is a number. Long identifiers, date-like strings, whitespace and negative numbers such as -12.50 remain text too. Formula-looking content is stored as text, not as an executable formula.

No cleanup or number/date inference is applied. Calculations may require deliberately converting selected cells later; leave identifiers as text. The converter creates a new workbook from CSV values; it does not preserve arbitrary Excel formatting, styles or formulas from an existing workbook.

Processing stays in browser memory. The original file remains unchanged and can be downloaded unchanged. Current limits include UTF-8 input up to 2 MB, 100,000 rows, 256 columns and 200,000 fields. See the converter’s supported-file limits for cell and output limits.

Try a small synthetic CSV

Copy this into a plain-text editor and save it as leading-zeros.csv using UTF-8. These invented records include a postal code, product identifier, account reference, long identifier and an ambiguous date-like string. Use a text editor to inspect the source rather than double-clicking it in Excel.

postal_code,product_id,account_ref,long_id,date_like
00123,000482,00007190,12345678901234567890,01/02/2026
00456,000901,00002841,98765432109876543210,03/04/2026

After text-preserving conversion, cell A2 should contain the five characters 00123, B2 should contain 000482, and D2 should contain all 20 digits of 12345678901234567890. The date-like field should remain exactly 01/02/2026.

How to verify that the zeros survived

1. Compare several cells with the unchanged CSV in a text editor, including the longest identifier and any empty fields.
2. Select a cell and inspect the formula bar as well as the grid. For the example above, both should show 00123.
3. In an unused cell, enter =ISTEXT(A2) and =LEN(A2). For the example’s postal code, expect TRUE and 5. Confirm the actual characters too: text type and length alone do not prove the value is correct.
4. Save as XLSX, close and reopen it, and spot-check again. Exporting back to CSV removes cell-type information; reopening that CSV directly can trigger conversion again.

Excel may flag “Number stored as text”. That is expected for an identifier. Choosing Convert to Number can remove the protection.

Troubleshooting and recovery limits

The cells show zeros, but are the values really text?

A custom format such as 00000 can display numeric 123 as 00123. The stored value is still a number. Changing an already-converted cell’s format to Text also does not put missing characters back. Microsoft explains the limits of formatting numbers as text.

A formula such as =TEXT(A2,"00000") can produce five-character text when an external specification requires exactly five digits. That applies a known rule; it does not discover the original value. Do not assume every identifier has the same width.

The original CSV is malformed or columns are shifted

Wrong delimiters, missing fields or unmatched quotes need structural review before conversion. The converter stops on malformed quoting. Open CSV Repair & Cleaner to inspect structural warnings, compare proposed changes with the source, and explicitly review any heuristic quote repairs. Download the reviewed CSV and convert that file separately. Not every malformed file can be repaired safely; ambiguous records may need manual correction. Keep the original unchanged.

The zeros or trailing digits are already gone

Return to the original export, a backup or the system of record. If the only remaining value is 123, it could have started as 123, 0123 or 00123. CSV Rescue cannot reliably reconstruct it. A verified external specification may allow padding to a known width, but missing trailing digits from numeric rounding require the original data.

Long IDs or dates changed instead

Excel numeric precision is limited to 15 significant digits. Scientific notation can be only a display choice, but a long identifier stored numerically may also have lost digits. Date interpretation can depend on locale: 01/02/2026 is ambiguous. Preserve these columns as text before interpretation; changing their display afterward cannot establish the original spelling or intended date.