Import CSV Into Excel Without Losing Leading Zeros

How-to basics · Updated November 18, 2026

To import a CSV into Excel without losing leading zeros, use Data > From Text/CSV in Excel for Microsoft 365 on Windows, choose Transform Data, set the identifier column’s data type to Text, and then load it. Avoid opening the CSV by double-clicking it: Excel may interpret values such as 00123 as numbers and change them to 123.

Why Excel removes leading zeros

A CSV file stores rows and columns as plain text separated by a delimiter, usually a comma. It does not normally contain Excel’s cell types, number formats, or instructions saying that a particular column is an identifier. When Excel opens a CSV, it tries to infer what each value represents. A string of digits may be interpreted as a number, and numbers do not need leading zeros, so 00123 can become 123.

This behavior is often inconvenient, but it is not necessarily a change to the original CSV. Excel may be displaying an imported interpretation of the data; the file on disk remains unchanged until you save over it. However, if you save the converted workbook back as a CSV, the changed value may be written out as 123. That is why it is safest to specify the column type during import, before the data is loaded into the worksheet.

Leading zeros are common in values that look numeric but are not used for arithmetic, including postal codes, product codes, employee numbers, account references, and telephone numbers. Treating these as text preserves the exact characters. By contrast, a value such as a price or quantity is normally a number because you may need to calculate with it.

Recommended method: import the CSV with Power Query

In Excel for Microsoft 365 on Windows, the From Text/CSV command opens an import preview. Choosing Transform Data lets you set the column type before loading. This is a dependable approach for a one-time import and is especially useful when you expect to repeat the import later.

  1. Open a blank workbook or the workbook where the data should go. Select Data > Get Data > From File > From Text/CSV, then choose the CSV file.
  2. Check the preview for the right delimiter, such as comma or semicolon, and confirm that the columns are separated as expected. Choose Transform Data rather than loading immediately.
  3. In the Power Query Editor, find the column that contains the codes or identifiers. If a Changed Type step has already converted it to a number, remove that step using the query’s Applied Steps pane. Otherwise the original zeros may already have been discarded within the query.
  4. Select the identifier column, then choose Transform > Data Type > Text. If Excel asks whether to replace the existing step or add a new one, make sure the final type is Text and that no earlier numeric conversion remains.
  5. Check the preview to confirm values such as 00123 still have their zeros. Choose Home > Close & Load to put the result in Excel.

Power Query’s exact ribbon layout can vary slightly between updates, but the important actions are to remove any automatic numeric type conversion and set the relevant column to Text before loading. If Excel has already changed the value to 123 in a worksheet, changing that cell’s format to Text does not recreate the missing zeros. You need the original CSV again or another reliable source for the intended code.

Choose Text for identifiers, not just a display format

Excel’s Text type preserves the characters as data. That distinction matters: a postal code such as 02108 is an identifier, not the number twenty-one thousand and eight. It should remain 02108 even if you sort it, export it, or compare it with another code. Text is also appropriate when a column contains a mix of digits and letters, such as 0012A.

A custom number format is a different option. For example, formatting the number 123 with the custom format 00000 displays it as 00123, but its stored value is still the number 123. That can be useful if the values are genuinely numbers and every value should display at a fixed width. It is not a substitute for text when the exact character sequence must survive copying, exporting, or joining with data from another system. A CSV does not preserve Excel’s custom number formats, so an export may contain 123 rather than 00123.

  • Choose Text for postal codes, IDs, telephone numbers, SKUs, and other labels whose digits are not used in arithmetic.
  • Choose a number format only when the value is truly numeric and the zeros are a display convention, not part of the identifier.
  • Check the source specification if code widths vary. Do not add zeros to every value just because one example has them.

What to do if you already opened the CSV

If you opened the CSV directly and Excel removed zeros, do not assume that selecting the cells and applying Text will restore them. The original string 00123 and the number 123 are different values; once Excel has interpreted the former as the latter, the worksheet no longer contains enough information to know how many zeros were present. Close the workbook without saving changes, then import the original CSV through the text-import process.

If you already saved over the source CSV, look for an unchanged copy, a backup, or a copy supplied by the system that created it. When the identifier has a known fixed width, a formula can rebuild a display string from the numeric value. With the imported number in A2 and a five-character code required, enter this in B2: =TEXT(A2,"00000"). If A2 contains 123, the formula returns the text value 00123. This method is only safe when five characters is the correct width for every relevant code; it cannot determine whether the original value was 00123, 0123, or 123 without that information.

For future imports, keep the original file untouched until you have checked the result. If you need to edit data in Excel and also deliver a CSV, retain a separate workbook copy for formatting and formulas, and verify the exported CSV in a plain-text editor or reimport it to check that the identifiers survived.

Repeat imports and automatic conversion settings

If you regularly receive a file with the same columns, Power Query can save the import and transformation steps in the workbook. When the source data is updated, use Data > Refresh All to run the query again. This reduces the chance of forgetting to set the identifier column to Text each time. Refreshing uses the saved query steps, so confirm that the source file is in the expected location and that the query still points to the intended file.

Some Microsoft 365 Excel for Windows builds also provide controls for automatic data conversion under File > Options > Data. Depending on the installed build, these settings can warn about or limit conversions such as removing leading zeros when opening CSV or similar files. They can help prevent surprises, but they are not a replacement for assigning the correct data type to each column during a structured import. Settings may differ by update and organization policy.

Excel for Mac has different menu layouts and feature availability can depend on the Microsoft 365 update. If the Windows path is not present, look on the Data tab for the text or CSV import option available in that version, and use its transformation or import settings to designate the identifier column as text before loading. Excel for the web is not the best choice for a controlled CSV import when preserving identifiers is essential; use the desktop application and inspect the imported values.

Delimiters, encoding, and other import checks

Preserving zeros is only one part of importing a CSV accurately. A file may use semicolons or tabs instead of commas, and it may use a character encoding that affects accented names or symbols. Review the preview before loading. If a whole row appears in one column, the delimiter is likely wrong. If characters appear garbled, check the file origin and select an appropriate encoding in the import experience when that option is available.

Quoted CSV fields do not reliably tell Excel to treat a numeric-looking value as text. Quotes are part of CSV syntax for handling delimiters, line breaks, and quotation marks inside fields; they are not a dependable declaration of an Excel cell type. For example, a CSV field written as "00123" can still be interpreted as a number by Excel. Set the column type explicitly instead.

Also check whether an identifier column contains blanks, unusually long digit strings, or mixed formats. Excel’s numeric storage has limits on precision, so long digit sequences should be imported as text rather than numbers even if they have no leading zeros. Preserve a copy of the original source if exact values are important, and compare a few representative rows after loading.

Alternatives and common misconceptions

For a small one-off paste, you can format a blank worksheet column as Text before entering or pasting values. Select the destination column, choose Home > Number Format > Text, then paste the data. This can work when the values are pasted as text, but it does not reliably control how Excel interprets a CSV opened directly, so the import workflow is safer.

Another possible route in some Windows installations is the legacy Text Import Wizard. It may need to be enabled through File > Options > Data by selecting the option to show legacy data import wizards. In the wizard, choose the delimiter, select the identifier column in the preview, set its column data format to Text, and finish the import. Availability varies, and the From Text/CSV Power Query route is generally the clearer choice in current Microsoft 365.

Do not add an apostrophe to every value in the source CSV as a general fix. Depending on how the file is created and imported, the apostrophe may become part of the data rather than serving as Excel’s worksheet-entry marker. Likewise, setting a worksheet column to Text after a numeric conversion cannot restore information already removed. The reliable principle is to preserve the value as text before Excel converts it.

Frequently Asked Questions

Can I import a CSV and keep leading zeros without a formula?
Yes. Import through Data > From Text/CSV, use Transform Data, and set the identifier column’s type to Text before loading. That keeps the zeros in the imported value rather than rebuilding them afterward.

Why does opening a CSV by double-clicking remove zeros?
Excel guesses data types when opening a plain-text CSV. It may decide that a digit-only field is a number and discard zeros at the start because they do not change the numeric value.

Will formatting the cells as Text bring back zeros that disappeared?
No. Text formatting affects how future values are handled; it does not know the original width of a value that Excel has already converted. Reimport the original file or reconstruct the code only if its correct width is known.

Does putting quotes around a CSV value preserve its zeros?
Not reliably. CSV quotation marks delimit a field but do not consistently specify Excel’s data type. Set the column to Text during import instead.

Should I use Text or a custom format such as 00000?
Use Text when the digits identify something and must remain exactly as supplied. A custom number format only changes display and is suitable only when the underlying value is truly numeric and the fixed-width display is all you need.

Will the zeros remain if I save the workbook as CSV?
They will remain if they are part of the text value. Zeros shown only by a custom number format may not be included, because CSV stores values rather than Excel’s display formatting. Check the exported file before relying on it.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.