Type 00123 into a fresh Excel cell. Hit Enter. Watch what happens.

The zeros vanish. Excel, in its quiet, opinionated way, decided those leading digits were meaningless and lopped them off. You are left with 123. Helpful? Not when you are working with zip codes, employee IDs, product SKUs, or any identifier where the zero is part of the value itself. So how do you stop Excel from doing this — and how do you put the zeros back when they have already disappeared?

There are five reliable ways, and each one has a moment when it's the right tool. The Format Cells Custom trick is fastest for display. The TEXT function is the one to use when you need a real string for export. CONCATENATE works for irregular padding. The apostrophe prefix is the quick-and-dirty fix for a single cell. And Power Query — well, Power Query saves your skin every time you import a CSV.

This guide walks through all five, plus the most common screw-up (importing a file and losing every zero in column A) and how to claw the zeros back if they are already gone.

Why Excel Drops Leading Zeros (And When It Matters)

Excel is, at heart, a number cruncher. The moment you type something that looks like a number, it converts the cell to the General or Number format. And in number-land, 007 and 7 are mathematically identical, so the leading zeros get tossed. Fine — until the data is a zip code starting with 0, a UK postal segment, a phone-system extension, or a part number where the position of every digit carries meaning.

Three places this bites people hardest:

• Northeast US zip codes. Massachusetts, Maine, Vermont, New Jersey, parts of Connecticut and New Hampshire all use 0-prefixed codes. Open a customer list in Excel and 02134 silently becomes 2134.
• Employee or student IDs. HR systems often pad IDs to a fixed width (say 6 digits). When the spreadsheet drops the zeros, joins to the source system break.
• Product SKUs. Inventory codes like 0040-A or 00891 get truncated, then matched to the wrong product in vlookups.

The fix you pick depends on one question: do you need the zeros to display only, or do you need them to exist as part of the actual value? That distinction matters more than most tutorials let on. A custom format like 00000 makes the cell look like 02134 but the underlying value is still 2134 — copy it to another app and the zeros disappear again. The TEXT function, by contrast, returns a real string. Pick the wrong tool and you'll be debugging missing zeros all afternoon.

Method 1: Format Cells > Custom (The Fast One)

This is the method most Excel users reach for first, and for good reason — it takes about four clicks and works on entire columns instantly. The catch, again: it only changes the appearance.

Here's the click path:

1. Select the cells (or the whole column header) you want to format.
2. Right-click and choose Format Cells, or hit Ctrl+1.
3. In the dialog, click the Number tab, then pick Custom at the bottom of the left list.
4. In the Type: field, type a string of zeros equal to the width you want. For 5-digit zip codes: 00000. For 6-digit employee IDs: 000000.
5. Click OK.

That's it. Any number narrower than the format string gets left-padded with zeros. Type 7 into a cell formatted as 00000 and you'll see 00007.

You can also use the ? placeholder if you want spaces instead of zeros for the missing positions, but for our purpose, plain zeros are what you want.

What about mixed formats?

Need a dash in the middle, like part numbers in the shape 00-1234? Set the custom format to 00"-"0000. Excel will treat the dash as a literal and pad the rest with zeros. Phone-style? (000) 000-0000 works on a raw 10-digit number.

Method 2: The TEXT Function (When You Need a Real String)

This is the one to use when the zeros must survive — when the file gets exported to CSV, sent to a database, or pasted into a system that doesn't honor Excel formats.

Syntax is straightforward:

=TEXT(A1, "00000")

If A1 contains 2134, the formula returns the string "02134". Not a formatted number. A real string. You can copy it, paste it as values, and the zeros stay.

The format code inside the quotes follows the same rules as the Format Cells dialog. "0000" pads to 4 digits. "000000" pads to 6. You can mix in literals: =TEXT(A1, "EMP-000000") on the value 47 returns EMP-000047.

A real workflow

Say column A has raw IDs (47, 1208, 39, 9911) and column B should display the padded version for export. In B2:

=TEXT(A2, "00000")

Drag down. Column B is now text. Select column B, copy, then Paste Special > Values over itself if you want to drop the formula. Save as CSV — the zeros will be in the file, exactly as displayed.

One gotcha: a TEXT-padded cell is left-aligned (because it's text), while a custom-formatted cell stays right-aligned (because it's still a number). If alignment matters for a report, mind that.

Method 3: CONCATENATE (and CONCAT) — When Padding Isn't Uniform

Sometimes you don't want every number padded to the same width. Maybe the rule is "prefix one zero to anything under 100" or "add 'ID-' and two zeros to short codes." That's where CONCATENATE earns its keep.

The simplest version:

=CONCATENATE("0", A1)

That sticks a single zero on the front, no matter what's in A1. The newer equivalent is =CONCAT("0", A1) or just ="0"&A1 using the ampersand operator. All three return identical results.

Where CONCATENATE shines is when you wrap it in an IF for conditional padding:

=IF(LEN(A1)<5, REPT("0", 5-LEN(A1)) & A1, A1)

That formula pads A1 with just enough zeros to reach 5 characters — no more, no less. If A1 already has 5 or more digits, it's left alone. REPT("0", n) repeats the zero character n times; subtract the existing length from 5 to figure out how many you need.

This pattern handles messy real-world data where some rows already have zeros and others don't. TEXT() can do it too, but the IF/REPT combo is easier to debug when you have to explain it to someone six months from now.

Method 4: The Apostrophe Prefix (Quick & Dirty)

If you only need to fix one or two cells and you're not going to do this again, the apostrophe trick is the laziest path. Type a single straight quote, then the value:

'02134

Press Enter. The apostrophe disappears from view. Excel quietly tags the cell as text, leaves the zero alone, and you're done. You'll see a small green triangle in the upper-left corner of the cell warning you that "the number in this cell is formatted as text or preceded by an apostrophe" — ignore it, that's the point.

Why it's useful: when you have a stray entry inside an otherwise numeric column and you don't want to reformat the whole column. Why it's not great: doesn't scale, doesn't survive some paste operations to other apps, and some validation tools (security scanners on uploaded files, for example) flag leading apostrophes as suspicious.

Method 5: Power Query for CSV Imports

Power Query is the grown-up answer to the CSV problem. It's been baked into Excel since 2016 and lives under Data > Get & Transform Data. Use it any time you're pulling external data — CSV, JSON, web tables, another workbook.

Workflow:

1. Data > Get Data > From File > From Text/CSV. Pick your file.
2. Excel shows a preview. Click Transform Data (not Load) to open the Power Query Editor.
3. Right-click the column header containing your padded IDs.
4. Choose Change Type > Text. If Excel asks whether to replace the current step or add a new one, choose Replace current — you want the type set on import, not after.
5. Click Close & Load. Your data lands in a worksheet with the zeros preserved.

The advantage: the import steps are saved. Refresh the source file and Excel reapplies the type rule automatically. No more manual reformatting every Monday morning.

Removing Leading Zeros (When You Need to Reverse Course)

The other half of this problem — somebody handed you a sheet with zeros padded in, and you need numeric values for math. The fix is the VALUE function:

=VALUE(A1)

If A1 contains the text "00789", VALUE returns 789 as a real number. Drop it in a helper column, paste-special as values back over the original, and you're done.

VALUE chokes on anything non-numeric. If a cell contains "00789-A", you'll get a #VALUE! error. For mixed content you'll need a more careful formula — typically =--A1 works for pure-numeric text (the double negative coerces to number), or you can wrap with IFERROR to handle bad rows gracefully.

Another approach: select the column, then Data > Text to Columns > Finish. That forces Excel to re-parse each cell. Numeric-looking text gets converted to numbers, zeros drop off, you're back to where you started.

Which Method Should You Actually Use?

If the file never leaves Excel: Custom format. It's the cleanest, the original number is preserved, and you can change the format later without touching the data.

If the file will be exported or shared with non-Excel systems: TEXT function. Real string, real zeros, survives the trip.

If you're importing a CSV with zero-padded IDs: Power Query, every time. It's the only method that prevents the zeros from being stripped before you even see the data.

If it's a one-off cell and you'll never do this again: apostrophe. Get on with your day.

And if the file is irregular — some rows pre-padded, others not — CONCATENATE with IF/REPT handles the mixed case better than any other single method.

Common Mistakes & How to Avoid Them

Mistake 1: Applying a custom format then exporting to CSV. The custom format is an Excel-only artifact. CSV is plain text, and the underlying numeric value is what gets written. If you need the zeros in the file, use TEXT() before exporting, or store the column as text from the outset.

Mistake 2: Using ="0"&A1 when A1 might already start with zero. You'll end up with double-padding for some rows and single for others. Always normalize first — use a LEN check or jump straight to TEXT() with a fixed width.

Mistake 3: Forgetting that TEXT output is text. Pivot tables and SUMIFS will treat "00789" as a different value from 789. If the column will be aggregated, decide upfront whether you want the display-only route (Custom format) or accept that you'll need VALUE() wrappers in downstream formulas.

Mistake 4: Double-clicking CSVs. See the Power Query section above. Open Excel first, import via the Data tab, and set the column type to Text in the preview. Five extra seconds saves an hour of cleanup.

Mistake 5: Mixing methods within one column. Some cells text-typed, some still numeric with custom format — VLOOKUP and INDEX/MATCH will silently fail to find matches because "02134" and 2134 aren't equal. Pick a method, apply it to the whole column, and stick with it.

Excel's behavior around leading zeros has been the same since at least the 2003 release, and Microsoft has shown no inclination to change it. The good news: every version since 2016 ships with Power Query, which is by far the most reliable defense. Learn the import workflow once and the CSV problem stops happening.

Quick Reference: Pick Your Method

Stuck mid-task? Here's the 30-second decision tree.

• Need it now, just for display? Ctrl+1, Custom, type zeros. Done.
• Exporting to CSV or another system? =TEXT(A1,"00000") in a helper column.
• Importing a CSV with zip codes or IDs? Data > Get Data > From Text/CSV, set column type to Text in the preview.
• Fixing a single rogue cell? Apostrophe prefix.
• Mixed-width data, conditional padding? =IF(LEN(A1)<5, REPT("0",5-LEN(A1))&A1, A1).
• Removing zeros you no longer want? =VALUE(A1), then paste values back.

Each of these is a 30-second fix once you've done it twice. The hardest part is remembering which one to reach for — and now you don't have to.

A Note on Excel for Mac and Excel Online

The Custom format dialog and TEXT function behave the same way on macOS Excel as they do on Windows — same syntax, same outcome. Power Query is the exception. On older Mac builds Power Query was missing or limited; the modern Microsoft 365 builds for Mac now ship a workable subset, but if you're stuck on Excel for Mac 2019 or earlier, the From Text/CSV import dialog still gives you per-column data-type selection at import time, which is a perfectly adequate substitute.

Excel Online — the browser version — is the most restricted of the three. The Format Cells dialog and TEXT function both work, but Power Query is unavailable. If your team works primarily in the browser version, the safest pattern is to do the CSV import in desktop Excel, save the file as XLSX with the type already set, then continue in the browser. Web-only users should also know that the apostrophe trick still works there exactly like it does on the desktop.