Keep leading zeros when importing a CSV into Excel

Import product codes as text, check their exact characters, and avoid inventing missing zeros after a numeric conversion.

A product code such as 00127 is a label. It does not become the same label when Excel displays 127. Set the code column to Text during import, before a number conversion can remove its zeros.

This guide uses deliberately different code lengths. That matters: padding every value to five digits would incorrectly turn both 00127 and 0127 into the same code.

Download a known example

Download identifier-practice.csv. It is a UTF-8 file with BOM, containing six fictional records and four columns. Keep an untouched copy.

The reference_id column contains long identifiers for the companion guide. Treat that column as Text too.

Get to an import preview first

Start with the downloaded CSV, not a workbook in which the zeros have already disappeared. The recorded Mac path used File → Open to reach the Text Import Wizard. If your version opens the file directly, use its text-import command with a column-type preview instead; see the encoding guide for the tested wizard context. Stop before loading if you cannot set both identifier columns to Text. Changing their format after import cannot restore missing characters.

Import the code column as text

This path was checked in Excel for Mac 16.112.2 on macOS 26.6.2. The test used a Chinese interface; the English labels here translate the observed controls. All six product-code values matched the sample after import.

In the three-step Text Import Wizard, use these settings:

1. Choose Delimited, start at row 1, and choose Unicode (UTF-8) for this sample.
2. Select Comma as the delimiter and keep the double quote as the text qualifier. Leave “treat consecutive delimiters as one” off so empty fields keep their position.
3. Select the product_code column in the preview and choose Text. Apply Text to reference_id as well, then finish the import.

Set both identifier columns

Select each column separately before finishing the wizard. Choosing Text for one column does not establish the type of every other column. This table summarizes the settings; it is not an interface screenshot. If your edition opens a CSV directly without showing the wizard, use its Data text/CSV import command instead. Do not rely on a double-click to choose the column type for you.

For Power Query, use the import preview to open the query editor and set the code column to Text before loading. Inspect any automatic Changed Type step: if it has already converted the code into a number, replace or remove that conversion rather than simply adding a text conversion afterward. The Power Query route is documented by Microsoft; it has not been tested for this guide. Microsoft's import guidance

Check exact values and lengths

Compare the imported column with the table above. If headers are in row 1, B2 should contain 00127, while B4 should contain 0127 and B5 should contain 127.

You can check a nonblank cell in an unused column with:

=EXACT(B2,"00127")
=ISTEXT(B2)
=LEN(B2)

For the first sample code, the expected results are TRUE, TRUE and 5. These are optional checks; do not paste them over the sample. The formulas use English function names and commas. Some regional installations use semicolons between arguments.

For the six sample rows, the expected lengths are 5, 5, 4, 3, 5 and 0. Check the empty last row separately: a blank is not the text 0.

Diagnose a failed check

Enter each optional check in a separate empty cell. Do not paste the three formulas into one cell or overwrite columns A:D.

What if the zeros have already disappeared?

If the original CSV still contains them, reimport that file as text. Reformatting the altered workbook does not read the missing characters back from the source.

If the original has been overwritten, 127 alone does not tell you whether the code used to be 127, 0127, 00127 or something else. Obtain an intact export or a trusted reference list.

Only pad codes when a verified rule says every code must have the same width. For a known five-character code system, a formula such as =TEXT(A2,"00000") can construct that representation. It is not a general recovery method and would be wrong for this sample's mixed-length codes.

Display formatting is a different choice

A custom number format such as 00000 can make the numeric value 127 look like 00127 inside a workbook. The underlying value is still numeric. Use text when the exact code must survive copying, matching or transfer into another system.

Putting quotation marks around a field in CSV is not a dependable way to declare an Excel text type. CSV quoting controls field boundaries; the importer still decides the data type. Inserting an apostrophe into a CSV can also become a literal part of the identifier in another application.

Before you hand the file to someone else

Once the codes are preserved as text, compare them against another list to distinguish absent keys from duplicate or differently cased keys.

• Keep an original export and your working workbook separately.
• Compare every code in a small sample, including blanks and all-zero values.
• Reimport a CSV export using the intended settings and check it again.
• Tell the recipient which columns are identifiers and need Text import.

For longer codes, continue with Keep long ID numbers intact in Excel. The risk there includes precision loss, not just leading zeros.