Excel Removes Leading Zeros From ZIP Codes and Phone Numbers: How to Stop It

Export a customer list from some ageing system, open the CSV in Excel, and something quietly breaks. Every ZIP code from Boston, Providence or Newark is four digits instead of five. 02134 now reads as 2134. The phone column that started with a 0 lost it too. Nobody edited those cells. Excel did it the moment the file opened.

This is the most common spreadsheet complaint I hear from people cleaning data, and the maddening part is that it looks like your mistake. It isn't. Excel guesses the type of everything it reads, and its guess for anything spelled with digits is that it must be a number. Numbers don't carry leading zeros, so off they come.

Below is exactly why it happens, which columns get hurt, and the fixes ranked by how much I'd trust each one. Two of them stop the damage before it starts. One gets the zeros back afterwards, but only sometimes. Knowing which situation you're in is most of the battle.

Why Excel removes leading zeros in the first place

A CSV is plain text with separators. There's no hidden flag on a field that says leave this one alone, treat it as text, because the format has no way to carry that information. So when Excel reads 02134, it has to decide what the value is, and it decides from what the characters look like. Digits look like a number. Excel's default General format prints numbers with no padding, and a leading zero has no numeric weight, so it's discarded on sight.

The same reasoning flattens 0044 to 44 and a padded part number like 00815 to 815. Nothing is malfunctioning. Excel is doing precisely what it thinks you want, which is the whole problem.

Where Excel removes leading zeros, and the columns that get hit

The columns that suffer are the ones where digits are labels, not quantities. A ZIP code isn't a number you'd ever add or average. Neither is a phone number, an employee ID, a bank routing number, a barcode or an SKU. Excel can't tell a label from a measurement.

ZIP codes are the classic casualty because a large slice of the country starts with zero. Every code in Massachusetts, Rhode Island, New Hampshire, Maine, Vermont, Connecticut and New Jersey begins with 0, along with Puerto Rico and parts of New York. Open a national mailing list and the entire Northeast drops a digit while the West Coast looks perfectly fine, which is exactly why the bug survives a quick glance.

Phone numbers go the same way, especially international ones stored with a leading 0 for the trunk code. So does any identifier that zero-pads to a fixed width: 00042 becomes 42, 000815 becomes 815, and the alignment your source system worked to keep is gone.

It's not hidden, the zero is actually gone

Here's the misread that costs people an afternoon. They assume the zero is still in the cell and merely not showing. It isn't. Once Excel decides 02134 is the number 2134, the cell holds 2134. Save the file and it writes 2134 to disk. Reopen it and there's nothing to recover, because the original text was never stored in the first place.

That's why setting the column to Text after the import fixes nothing. A number format only changes how an existing value looks, and it applies to values entered after you set it, not before. 2134 formatted as text is still 2134. The fix has to land before or during the import. Afterwards is too late.

The bigger trap: numbers past 15 digits

Leading zeros are irritating but usually recoverable. There's a nastier version of the same behaviour that isn't. Excel keeps 15 significant digits, full stop. That's a hard limit inherited from the floating-point standard it uses, not a preference you can nudge. Anything longer has its extra digits rounded away to zero.

A 16-digit card number ending in 1 comes back ending in 0. A 19-digit reference lands as 1.23457E+18, because Excel flips long numbers into scientific notation, and if you save to CSV it writes that shortened form straight to the file. There is no undo here. The discarded digits were never held anywhere. If a column carries card numbers, IMEIs, long account references or anything over 15 digits, it has to be text from the first moment it enters the sheet. The leading-zero fixes below apply here too, except here they aren't optional.

When a code turns into a date

Excel doesn't stop at zeros. A value that merely resembles a date gets rewritten as one. 03-04 becomes a date serial, and a short code like 1-5 turns into 5-Jan. It's the same guess-the-type reflex, confident and silent. This is the mechanism that pushed geneticists to rename genes because Excel kept reading symbols such as SEPT1 as 1-Sep. For your data the lesson is identical to the ZIP problem: marking the column as text before anything loads is the only thing that stops the autocorrect firing.

Five fixes for keeping the zeros, ranked

Here's what actually works, best first. Best means it protects you at import, needs the least repeating, and doesn't lean on the values being recoverable.

The top method is the one worth memorising. Go to Data > Get Data > From File > From Text/CSV, pick your file, and in the preview window click the header of each ID column and set its type to Text before you hit Load. That single step tells Excel not to guess, and it's the only approach that protects the values as they come in rather than trying to rescue them later.

Turn the conversion off for good (Excel 365 and 2024)

If you open CSVs constantly, Microsoft finally shipped a set of switches for this. In Excel for Microsoft 365 and Excel 2024, on Windows and Mac, go to File > Options > Data > Automatic Data Conversion. There are four toggles, and the ones that matter for this problem are the first two:

• Remove leading zeros from numerical text and convert to a number
• Truncate numerical data to 15 digits of precision and convert to a number that may be shown in scientific notation
• Convert numerical data around the letter E to scientific notation
• Convert a continuous string of letters and numbers to a date

Untick the first to keep your ZIP codes, and the second to keep long IDs whole. There's also a checkbox that makes Excel warn you, per file, when it's about to convert something on load, with a one-time option to load without converting. On any older Excel these switches don't exist, and you're back to the import methods above.

Getting the zeros back after they're gone

Sometimes the file has already been opened and saved, and you're looking at four-digit ZIPs with no source to re-import. If, and only if, the values are a known fixed width, you can rebuild the display with a custom format. Select the column, press Ctrl+1, choose Custom, and type 00000 for five-digit ZIPs. Excel pads the shown value back out, so 2134 reads as 02134 again.

The catch is that this is display padding, not real recovery. It works for a ZIP because the width is always five, so the missing character isn't ambiguous. It falls apart for anything variable-length, an ID that might be 00042 or 0042 has no single correct answer, and it does nothing for numbers truncated past 15 digits, because those are genuinely gone. When you need the padded value baked in for a clean re-export, put =TEXT(A1,"00000") in a helper column so the string itself carries the zeros.

Hand Excel a file that's already text

There's a way to sidestep the whole fight: don't give Excel a CSV to guess at. Convert the CSV to a real .xlsx first, and the file arrives with every column's type already decided, so Excel opens it without re-parsing anything.

That's the idea behind a CSV to Excel converter that writes every cell as text. It builds the spreadsheet with each cell marked as a string, so 02134 stays 02134 and a 16-digit number keeps all 16, because the value is text from the moment Excel sees it and there's nothing left to autocorrect. Everything lands on one sheet, and it works out the delimiter by reading your header row, so the semicolon files that European copies of Excel produce load without any fiddling. The honest trade-off mirrors the problem itself: since every cell is text, a genuinely numeric column comes in as text too, so a SUM won't add up until you flip that one column back to numbers. For files that are mostly codes, IDs and dates you want kept exact, that's a fair swap.

Google Sheets and LibreOffice aren't Excel

If you're not tied to Excel, the other two big spreadsheets put the switch right in front of you. Google Sheets, under File > Import, has a Convert text to numbers, dates, and formulas option. Turn it off and the zeros survive; it also has Format > Number > Plain text for whole columns. LibreOffice Calc goes further still: open a CSV and it shows an import dialog every time, where you click a column and set it to Text before a single row loads. No silent guess, no menu hunting. For a file you already know is full of fragile IDs, opening it in Calc first is often the fastest safe route.

The rule that prevents all of this: a ZIP code, a phone number, an account reference and a barcode are not numbers. They're labels written with digits, and any tool that treats them as quantities will round them. Decide the column is text before the data lands, and Excel has nothing left to guess at. Do that, and you'll stop hearing about the four-digit ZIP codes from the person you sent the file to.