Why does Excel remove leading zeros from CSV files?

CSV files are plain text with no cell type information. When Excel opens a CSV by double-clicking, it automatically guesses column types. If a column looks numeric, Excel converts it to a number — and numbers do not have leading zeros. A value like 00045 becomes 45. To prevent this, import the CSV using Data > From Text/CSV and set the column type to Text manually.

How do I open a CSV in Excel without losing leading zeros?

Do not double-click the file. Instead, open Excel first, go to Data > From Text/CSV, select the file, and in the Power Query import dialog set the columns containing IDs, ZIP codes, or codes to Text data type. This tells Excel not to convert those columns to numbers, preserving all leading zeros.

How do I add leading zeros back to a column in Excel?

If zeros were already removed, use TEXT to add them back: =TEXT(A2,"00000") for a 5-digit zero-padded value. Alternatively, apply a custom number format of 00000 to the column so it displays with leading zeros. Note: number formatting only changes the display — the stored value remains without zeros. For actual text with leading zeros, use the TEXT formula.

How to Keep Leading Zeros in CSV Files When Opening in Excel

Why Excel Removes Leading Zeros

CSV files are plain text. The file contains no information about whether a column is text, a number, a date, or a currency value. When you double-click a CSV file, Excel uses a built-in type-detection algorithm to guess what each column contains.

If a column contains values like 00045, 00123, 01002, the algorithm sees "these look like numbers" and converts them to integers: 45, 123, 1002. The leading zeros are gone. The file on disk still contains the original zeros — only the Excel representation loses them.

This is not a bug. It is Excel optimizing for the common case where a column of numbers should be stored as numbers, not text. The problem is that it is the wrong optimization for identifier columns, and there is no way to override it when you open a CSV by double-clicking.

Common Cases Where Leading Zeros Get Removed

US ZIP codes: ZIP codes like 07001 (New Jersey) or 02101 (Massachusetts) start with 0. After Excel strips the leading zero, the ZIP code is wrong.
Employee or student IDs: Organizations often pad IDs to a fixed length — 000145 for a 6-digit employee number. Stripping the zeros breaks lookups against HR systems.
Product codes and SKUs: Warehouse systems, ERP systems, and e-commerce platforms often use zero-padded codes like 0012345.
Bank account and routing numbers: Financial data frequently uses zero-padded values as identifiers.
Barcode values: GS1 and EAN barcodes commonly start with zeros.
Phone numbers: International phone numbers starting with 0 like UK numbers 07911 123456 lose their leading zero.

Method 1: Import Instead of Double-Clicking (Best Prevention)

The most reliable way to prevent the problem is to import the CSV instead of opening it directly.

Open Excel to a blank workbook. Do not open the CSV by double-clicking or dragging it into Excel.
Go to Data > From Text/CSV.
Browse to the CSV file and click Import.
Excel opens a preview dialog (Power Query). Check the column types shown at the top of each column.
Find the columns that contain identifiers with leading zeros. Click the data type icon at the top of those columns (usually showing "123" for numbers) and change it to Text.
Click Load to bring the data into Excel.

Once you set those columns to Text type, Excel treats every value as text and preserves the leading zeros exactly as they appear in the CSV file.

Method 2: Use Power Query for Repeatable Imports

If you import the same CSV file type regularly (such as a weekly export from your CRM or ERP), set up the import once in Power Query and save it. Every subsequent import refreshes automatically using the same column type settings.

Import the first file via Data > From Text/CSV.
In Power Query, right-click each identifier column header and choose Change Type > Text.
Click Close & Load to create the connection.
Next time you receive an updated file: go to Data > Queries & Connections, right-click the query, choose Edit, and change the file source to the new file path.

Method 3: Format Cells as Text Before Pasting Data

If you need to paste or type values with leading zeros into Excel manually:

Select the cells or columns where you will enter the data.
Right-click and choose Format Cells.
Go to the Number tab and choose Text.
Click OK.
Now type or paste the values. Excel treats them as text and preserves leading zeros.

Important: format the cells as Text before entering values, not after. If you enter a number and then format as Text, the number is already stored without leading zeros and formatting does not restore them.

Method 4: Prefix Values With an Apostrophe

When entering individual values, type an apostrophe before the leading zero:

'00045

The apostrophe tells Excel to treat the value as text. It does not appear in the cell — only the value 00045 is displayed. The cell will have a small green triangle indicating it is a text-formatted number, which you can ignore or dismiss.

This method is useful for one-off manual entries but is not practical for large datasets or CSV imports.

Method 5: Modify the CSV File Before Opening

If you cannot change how you open the CSV (for example, you receive it from another system and must open it directly), you can modify the CSV file first to force Excel to treat specific columns as text.

Open the CSV in a text editor (Notepad). Add quotes around values that contain leading zeros:

ZipCode,Name,Revenue
"07001",Acme Corp,45000
"02101",Beta Ltd,32000

Most CSV parsers (including Excel) treat quoted values as text. However, Excel does not always honor this for double-click opens — the safest approach remains using Data > From Text/CSV with explicit column type setting.

Method 6: Custom Number Format (Display Only)

If the zeros were already stripped but you need to display a fixed-length value, use a custom number format:

Select the affected column.
Right-click > Format Cells > Custom.
Enter a format string with zeros: 00000 for 5-digit display, 000000 for 6-digit.
Click OK.

The cell will now display 45 as 00045. But this is only a display change — the stored value is still 45 without leading zeros. Formulas that reference this cell will use 45, not 00045. Exports and copies will also use the underlying 45.

This approach is appropriate when you only need the display to look correct (for printing or visual review) and will not use the values in text lookups or exports.

Method 7: Restore Lost Leading Zeros With TEXT Formula

If the zeros were already removed and you need to recreate text values with the correct length:

=TEXT(A2,"00000")

This formats the number in A2 as a 5-character, zero-padded text string. The number 45 becomes the text "00045". Use the appropriate format string for your data:

5-digit ZIP code: =TEXT(A2,"00000")
6-digit employee ID: =TEXT(A2,"000000")
8-digit product code: =TEXT(A2,"00000000")

After applying the formula, paste the results as values to replace the formulas with the fixed text strings.

Note: this only works correctly if you know the expected length of each identifier. If different records have different valid lengths, you need a different approach — typically going back to the original source CSV and re-importing correctly.

How to Verify Leading Zeros Are Preserved After Import

After importing, verify that the values are correct:

Use =LEN(A2) on a few cells. The length should equal the expected width of the identifier. A 5-digit ZIP code should return 5; a value stored as 45 would return 2.
Check whether the cell value is left-aligned (text) or right-aligned (number). Text values are left-aligned by default; numbers are right-aligned.
Use =ISNUMBER(A2). If this returns TRUE for a ZIP code column, the values are stored as numbers and leading zeros are not preserved in the stored value.

The CSV to Excel Converter lets you preview data before converting and handles column type settings so you can verify leading zeros are preserved before downloading the final workbook.

Preventing the Problem When Exporting From Excel to CSV

If you are on the sending side — exporting data from Excel to CSV to share with another system — make sure leading zeros survive the export:

If identifier columns are stored as text in Excel, they export correctly to CSV with leading zeros intact.
If identifier columns are stored as numbers with a custom display format, the CSV export writes the underlying number (without leading zeros), not the formatted display string.
Before exporting to CSV, convert numeric identifier columns to text using the TEXT formula, then paste as values.

Frequently Asked Questions

Why do leading zeros disappear when I copy and paste from CSV into Excel?

When you copy text from a CSV in Notepad and paste into Excel, Excel applies the same auto-type-detection as when opening a file directly. Values that look numeric are converted. To paste as plain text, paste into a column that is already formatted as Text, or use Paste Special > Text.

Does saving as XLSX preserve leading zeros?

Only if the cells are stored as Text. If leading zeros are visible because of a custom number format (like 00000), saving as XLSX preserves the format — but if you later export to CSV, the zeros will be lost again because CSV does not store formatting. The underlying number value determines what appears in CSV exports.

How do I keep leading zeros in Excel permanently?

Store the values as text. Format the cells as Text before entry, import using Data > From Text/CSV with Text column type, or use the apostrophe prefix method. As long as the cell stores a text value, leading zeros are preserved through saves, copies, and reopens.

Can I use conditional formatting to detect missing leading zeros?

Yes. If ZIP codes must be 5 digits, apply conditional formatting with the formula =LEN(A2)<5. Cells shorter than 5 characters are highlighted as problematic. This helps identify which records lost their leading zeros after import.