Keep Leading Zeros in Excel CSV Imports and Exports

Decide whether the value is an identifier

Ask what a calculation on the column would mean. Adding two SKUs usually has no useful interpretation, while adding quantities does. That is why this sample needs different types in adjacent columns: sku is text and quantity is numeric.

Leading zeros may distinguish records. A supplier could use both 00123 and 123 as separate codes, or use a mixture of code lengths. Converting those values to numbers can collapse distinct identifiers. Do not infer a universal five-character rule just because the first few examples happen to be that length.

Import CSV into Excel without losing leading zeros

Start with the original CSV, not a CSV resaved after zeros disappeared. These steps keep the sample SKU as text while allowing quantity to remain numeric.

If the query already contains a Changed Type step that made sku numeric, remove that conversion and assign the types again from the unconverted source. Changing the already shortened value back to Text would only produce the text 123. Load the table and compare both SKUs character for character with the file.

1. In a new desktop Excel workbook, choose Data > From Text/CSV and select the original file.
2. Confirm the delimiter. For this sample, choose Comma and check that sku and quantity occupy separate columns.
3. If Data Type Detection is available, turn detection off so values initially remain text. Choose Transform Data.
4. In Power Query, select sku and set its data type to Text. Set quantity to Whole Number.
5. Choose Close & Load, then confirm the two SKUs are 00123 and 00456.

Use formatting only for its intended job

For new manual entry, select the empty destination range and choose Text in Format Cells before typing the codes. For a few manually entered values, an initial apostrophe is another Excel entry method. That entry convention is not a general CSV type declaration.

A custom number format such as 00000 changes the display of a numeric value. It can make 123 appear as 00123 in the worksheet, but it does not prove that the original code contained exactly two leading zeros. Use a real text identifier when another table, export, or lookup must receive the exact code.

Related help: Protect IDs with 16 or more digits from Excel conversion

Rebuild only a documented fixed width

Suppose a supplier confirms that every SKU consists of exactly five digits and the remaining digits are intact. In a helper column, TEXT can then apply that known rule. This is a reconstruction from a specification, not recovery of hidden characters. Keep the original shortened column until you have checked the rebuilt values.

Do not use a formula that blindly takes the last five characters; it can truncate a legitimate longer code. Investigate any input longer than the required width, negative value, decimal value, or non-digit character before padding.

If A2 contains numeric 123:
=TEXT(A2,"00000")
Expected text: 00123

If A3 contains numeric 456:
=TEXT(A3,"00000")
Expected text: 00456

Use only when five digits is the confirmed source rule.

Why do leading zeros disappear after saving as CSV?

After import, use ISTEXT(A2) to check the SKU type and compare A2 with the original 00123. A type check alone would also accept the wrong text 123, so it is not a value check. Confirm the second code as well and verify that quantities total 12.

A reopened worksheet is not enough evidence that the export lost its zeros. Open the saved CSV in a plain-text editor, locate the sample SKU, and follow the matching case below. Keep a working .xlsx copy when you need workbook formatting; CSV does not store an instruction that the next reader must treat a column as Text.

Stop automatic zero removal in Excel 365 or 2024

In Microsoft 365 or Excel 2024, Windows users can open File > Options > Data; Mac users can open Excel > Preferences > Edit. Under Automatic Data Conversion, disable the option that removes leading zeros.

Reopen an intact sample to check the result. This preference does not govern Power Query imports; keep their column types explicit. If the option is absent, use the import procedure above.

Know what a leading-zero warning establishes

Column Harbor can flag digit strings that may be at risk during spreadsheet import. The warning means the values deserve an explicit type decision; it does not mean the CSV itself is broken. The checker cannot restore zeros already removed from a saved file or assign Excel column types.