How to Add Leading Zeros to a CSV So They Don’t Vanish
The quick answer: a custom cell format such as Format Cells → Custom → 00000 only changes how a number looks — the cell still holds 501, so when you Save As CSV Excel writes 501 and the padding vanishes again. To make leading zeros stick you have to put the real text 00501 into the file and keep the column as text. The fastest durable route is to pad the whole column at once with our free leading-zeros tool and use its Excel-safe export, so a reopen can’t undo the work. This guide shows why the format trick fails and what actually survives the save.
Why the custom format doesn’t stick
A number format changes the display, not the data. When you apply Custom → 00000 to a cell that contains 501, Excel keeps storing the number 501 and merely paints five characters on screen: 00501. That is perfect for a printout. But a CSV file is plain text with no place to record a cell’s format — it stores only values. So the moment you choose Save As → CSV, Excel writes the underlying number, 501, and the two leading zeros you saw are simply not in the file. Re-open it and the column is short again.
This catches people because the spreadsheet looks completely fixed right up until the save. The padding was real on screen and fake in the file. To get zeros that survive, the cell has to contain the text 00501 — five actual characters — not a three-digit number wearing a costume.
Three methods, compared
Only one of the common approaches puts real zeros in the file and keeps them there through a save-and-reopen. Here is how the three stack up:
| Method | Produces real 00501? | Survives Save-as-CSV + reopen? |
|---|---|---|
Custom format 00000 | No — display only | No — saves as 501 |
=TEXT(A2,"00000") + Paste Values | Yes — real text | Only if you re-import the CSV as text |
| Browser pad tool with Excel-safe export | Yes — real text | Yes |
The middle row is the honest catch: =TEXT() genuinely creates the text 00501, but a plain CSV still carries no type information, so double-clicking the saved file re-runs Excel’s auto-conversion and can strip the zeros a second time. The Excel-safe export in the third row sidesteps that by writing a value Excel is forced to read as text.
Pad a column with the free tool
The most reliable route is to add the zeros to the raw file, not inside Excel. Our leading-zeros tool pads a whole column to the width you choose and can write each value in an Excel-safe form so a reopen can’t undo it. It runs entirely in your browser — the file never leaves your device.
- Open the leading-zeros fixer and drop your CSV onto the page.
- Pick the code column — ZIP, SKU, employee ID — and set the target width (for example
5for US ZIP codes,13for an EAN). - Turn on Excel-safe export, which writes each cell as
="00501"so Excel opens it as text. - Click Download. Every code is now real, fixed-width text baked into the file.
Pad the whole column in one pass
Skip the format-then-lose-it cycle. Drop your CSV in, choose the code column and width, tick Excel-safe, and download a file where the leading zeros are part of the data — all locally, nothing uploaded.
Open the leading-zeros fixer →The =TEXT() formula and its trap
If you would rather stay in Excel, the TEXT function is the right building block because it returns genuine text, not a formatted number. In a helper column enter =TEXT(A2,"00000") for a five-character ZIP, or =TEXT(A2,"0000000000000") for a 13-digit barcode. The result 00501 is real text, left-aligned, with the zeros in the value itself.
The trap is in what you do next. A formula result is still a formula: if you save now, Excel evaluates it and you are back to storing a number. So copy the helper column, then Paste Special → Values over the original, and delete the helper. Even then, one more gotcha remains — re-opening the saved CSV by double-click re-triggers auto-conversion, which can re-strip the zeros. The safe way to reopen is Data → From Text/CSV with the code column set to Text, covered in opening a CSV in Excel without breaking it. If Excel is instead flagging your already-correct codes with a green triangle, see number stored as text. And if this is really the opposite problem — zeros you wanted that keep disappearing — read why CSV leading zeros disappear.
Adding zeros in Google Sheets
Set the column to text before the data lands, or import with conversion off. Select the code column and choose Format → Number → Plain text, then type or paste your codes — 00501 stays 00501. If you are bringing in a CSV, use File → Import and untick “Convert text to numbers, dates and formulas” so every value arrives as written. To build the padding with a formula, =TEXT(A2,"00000") works the same way it does in Excel; copy the result and use Edit → Paste special → Values only before exporting so you are storing text, not a live formula. Sheets writes its export as plain CSV too, so re-importing that file still needs conversion turned off to keep the zeros.
Frequently asked questions
How do I add leading zeros to a CSV file?
Write real padded text into the file and keep the column as text. The quickest durable way is to run the CSV through a padding tool that sets each code to a fixed width — five characters for a ZIP, thirteen for an EAN — and exports an Excel-safe value such as ="00501". A custom cell format like 00000 only changes the display and is lost the instant you save as CSV.
Why do my leading zeros disappear again when I save the CSV?
Because the cell still holds the number 501; only its appearance was padded to 00501 by a format. CSV is plain text and stores no formatting, so Excel writes the underlying 501 and the zeros are gone. Only a value that is genuinely stored as the text 00501 survives the save.
Does Format Cells → Custom 00000 work?
Only for display. It makes the sheet look right and prints correctly, but the cell still contains the number 501, so the padding vanishes the moment you Save As CSV. To keep the zeros in the file, the cell needs to contain the actual text 00501, not a number with a format applied.
How do I add leading zeros in Google Sheets?
Set the column to Format → Number → Plain text before you type or paste, or import the CSV with “Convert text to numbers, dates and formulas” unticked. You can also build padded text with =TEXT(A2,"00000"), then copy and use Paste special → Values only before exporting so the result is stored as text rather than a formula.
Sources: the display-only nature of custom number formats and the Excel-safe ="00501" workaround are discussed in the Microsoft Q&A thread on leading zeros in CSV documents. Microsoft’s guidance on setting a column to Text when importing is in Import or export text (.txt or .csv) files.