Leading zeros disappeared — ZIP codes, SKUs, and phone numbers turned into shorter numbers

Restoring zeros that Excel already removed

If you know the correct length of the values in a column, you can pad them back. In a helper column next to your data, use the TEXT function. For five-digit ZIP codes:

=TEXT(A2,"00000")

The string of zeros inside the quotes tells Excel how many total digits the result should have — it adds leading zeros to reach that length. 1234 becomes 01234; 12345 stays 12345. For a 10-digit phone number with no separators, you'd use "0000000000". For a fixed-length product code of 8 characters, "00000000".

Once the helper column looks right, copy the entire column, then right-click the original column and choose Paste Special > Values to replace the old data with the corrected values. Delete the helper column.

Preventing this from happening again

The fix depends on how the data is getting into Excel.

If you're typing the values yourself: Format the column as Text before you start. Right-click the column header, choose Format Cells, select Text from the category list, and click OK. Now Excel will store whatever you type exactly as typed — zeros included.

If you're opening a CSV file: Don't double-click the file to open it. Instead, open Excel first, then go to Data > Get Data > From Text/CSV (or the equivalent Import Wizard in older versions). This gives you the option to tell Excel what type each column is. Mark the ZIP code or SKU column as Text during the import step, and the zeros will be preserved.

If the data is being pasted from somewhere: Format the destination column as Text first, then paste. If you paste into a General-formatted column, Excel may still auto-convert before the formatting kicks in. Set Text format first, paste second.

Why Excel does this in the first place

Imagine you wrote the number 007 on a calculator. The calculator would display 7 — the leading zeros are meaningless to a calculator because they don't change the value. Excel thinks the same way. When it sees 01234, it treats it like a math number, decides the zero at the front doesn't mean anything, and strips it. The result is 1234 — four digits instead of five.

The trouble is that ZIP codes, phone numbers, product SKUs, employee IDs, and social security numbers aren't math numbers. They're identification codes where every character is part of the meaning, and a missing leading zero turns them into the wrong code entirely. ZIP code 01234 (in western Massachusetts) is a completely different place from 1234 (which isn't a valid ZIP at all). Excel's helpfulness is actively wrong here.

Why you might not have noticed right away

Excel shows no error for this. The cell doesn't turn red. There's no warning dialog. The value just silently becomes shorter. If you're not comparing against a known-correct source, the problem can sit undetected for a long time — until addresses fail to match, shipments go to the wrong ZIP, or a product lookup breaks because the SKU no longer matches the warehouse system.

If you imported data from a CSV file, the stripping happens the moment Excel opens the file. By the time you're looking at the spreadsheet, the zeros are already gone.

What if the column has a mix of values — some needing zeros, some not?

This comes up with international addresses (some countries have numeric postal codes without leading zeros; others always require them) or with product catalogs where some SKUs are all-numeric and some have letters. In this case, the safest approach is to store the entire column as Text from the start. Text columns preserve whatever you put in them exactly. The trade-off is that you can't do arithmetic on text cells — but ZIP codes and SKUs aren't things you'd add up anyway.

If you have a large file and aren't sure which columns have lost leading zeros, the fix formula errors tool can inspect your spreadsheet and flag columns where values appear to be shorter than a consistent expected length — a common sign of stripped leading zeros in code-type fields.