How to Stop Excel from Removing Leading Zeros in CSV Files

You open a CSV of customer addresses in Excel and every New England ZIP code is broken. 02134 is now 2134. 06810 is now 6810. You didn't edit anything, you didn't run a formula, you just double-clicked the file. Excel destroyed the data on open, and if you hit Save, the damage is written back into the file permanently.

The same thing hits product SKUs that start with zero, phone numbers, bank routing numbers, employee IDs, and UPC/EAN barcodes. And it has an ugly sibling: any number longer than about 15 digits, like an Amazon order ID or a UPS tracking number, gets mangled into scientific notation (9.40011E+21) and the trailing digits are replaced with zeros. Here's exactly why Excel does this, how to work around it manually, and how to make sure it never happens to your files again.

What your CSV actually contains:

zip,sku,tracking_number
02134,00451-A,9400111899223197428490
06810,00078-B,9400111899223197428512

What Excel shows (and saves) after you double-click it:

zip,sku,tracking_number
2134,00451-A,9.40011E+21
6810,00078-B,9.40011E+21

Notice two separate failures in that example. The ZIP codes lost their zeros. The tracking numbers became identical scientific-notation blobs, because Excel rounded both to 15 significant digits and the last 7 digits, the part that made each tracking number unique, are gone. The SKU survived only because the hyphen and letter forced Excel to treat it as text.

Why Excel deletes leading zeros

A CSV file is plain text. It has no cell formats, no data types, no way to say 'this column is text.' When Excel opens one, it guesses the type of every cell. Anything that looks like a number gets converted to Excel's numeric type, and numbers don't have leading zeros. 02134 the text becomes 2,134 the quantity. The zero isn't hidden, it's mathematically gone.

The scientific notation problem is the same guess with a second layer of damage. Excel stores numbers as 64-bit floating point, which holds at most 15 significant digits. A 22-digit tracking number doesn't fit, so Excel keeps the first 15 digits, zeroes out the rest, and displays the result as 9.40011E+21. Even if you reformat the cell afterward, the true digits were discarded at the moment of conversion. There is nothing to recover.

The critical detail most guides skip: the conversion happens on open, not on save. By the time you're looking at the spreadsheet, your original values only exist in the untouched CSV on disk. Formatting the column as Text after opening does nothing, you're just formatting the already-corrupted numbers. The fix always has to happen before or instead of Excel's type guessing.

The data that gets silently damaged

Leading-zero and long-number corruption shows up anywhere an identifier happens to be all digits. These are the columns to audit before any import:

The worst part is that the damage compounds quietly. A bookkeeper opens a bank export to check one number, hits Save out of habit, and re-uploads it to QuickBooks. A seller edits three prices in a catalog file and re-imports it to their marketplace. The zeros were fine in the original export. They died in the sixty seconds the file spent open in Excel.

How to fix it manually in Excel and Google Sheets

There are legitimate manual workarounds. All of them share one requirement: you must never double-click the CSV. Once Excel has opened the file normally, everything below is too late.

Method 1: Import the CSV instead of opening it

In modern Excel, open a blank workbook, then go to Data > Get Data > From Text/CSV and select your file. In the preview dialog, click Transform Data to open Power Query, select the ZIP, SKU, and ID columns, and set their type to Text. In older Excel versions, the legacy Text Import Wizard does the same job: choose Delimited, then on the final step select each at-risk column and mark it as Text instead of General.

This works, and it's Microsoft's own recommended path. The problem is that it's a per-file, per-column ritual. You have to remember to do it every single time, correctly identify every at-risk column, and hope nobody else on your team double-clicks the file. One tired Tuesday and the zeros are gone again.

Method 2: Pre-format columns as Text, then paste

You can format empty columns as Text (Ctrl+1 > Text) in a blank sheet, then open the CSV in a plain text editor, copy the raw values, and paste them in. Tedious, error-prone for wide files, and completely impractical for anything over a few hundred rows, but it does preserve the values.

Method 3: Rebuild zeros with a formula (partial fix only)

If the zeros are already lost and you know every value should be a fixed length, you can reconstruct them. For 5-digit US ZIP codes, =TEXT(A2,"00000") pads 2134 back to 02134. This genuinely repairs fixed-length fields. It cannot repair variable-length values like phone numbers or SKUs, because Excel has no way to know how many zeros each one originally had. And it can never repair scientific-notation damage, because those trailing digits were rounded away, not hidden.

Google Sheets

Sheets makes the same guesses. When importing (File > Import), uncheck 'Convert text to numbers, dates, and formulas' and your zeros survive. But the same trap applies: if you or a teammate opens the file via drag-and-drop with conversion left on, or downloads back to CSV after Sheets has coerced the column, the values are altered. The manual fixes all work until the one time someone forgets.

The reliable fix: clean the file without Excel's type guessing

The root cause isn't your data, it's letting a tool that aggressively coerces types be the thing that touches your CSV. PipeSheets takes the opposite approach: when you upload a CSV or XLSX, values are preserved as-is. 02134 stays 02134. A 22-digit tracking number stays all 22 digits. There is no silent conversion step, so there is nothing to work around.

Here's the workflow for the two most common situations.

Protecting a file you haven't opened in Excel yet

Repairing ZIP codes that already lost their zeros

If the damage is already done to a fixed-length field like US ZIP codes, you can rebuild the zeros with PipeSheets' find & replace step in regex mode. A pattern that matches 4-digit values and prepends a zero fixes the entire column in one pass:

Step: Find & Replace (regex) on column "zip"
Find:     ^(\d{4})$
Replace:  0$1

Before          After
2134      →     02134
6810      →     06810
90210     →     90210   (5 digits, untouched)

Add a second pass with ^(\d{3})$ replaced by 00$1 to catch the handful of ZIPs with two leading zeros, like 00501. Because the anchors require the value to be exactly that many digits, valid 5-digit ZIPs pass through untouched. The same technique repairs any fixed-length code: 9-digit routing numbers, 12-digit UPCs, fixed-width employee IDs.

Standardizing the rest of the column while you're at it

Broken zeros rarely travel alone. The same exported files usually have trailing spaces that make '02134 ' fail an exact match, inconsistent casing in SKU suffixes, and headers like 'Zip Code ' with a trailing space that breaks column mapping on import. In the same PipeSheets pipeline you can trim whitespace across all columns, apply an upper-case transform to SKU columns so 00451-a and 00451-A stop being two different products, and normalize headers to snake_case so zip_code maps cleanly every time. Save the pipeline once and re-run it on every future export instead of rebuilding the fix by hand.

How to keep leading zeros safe going forward

A few habits eliminate this entire class of data loss:

The zeros in your data are not decoration. They're the difference between a package reaching Boston and an import error, between a SKU matching your catalog and a duplicate listing. Upload your file to PipeSheets, run Quick Clean plus a regex pad if you need one, and download a file where every identifier is exactly what the source system exported.