How to Keep Leading Zeros in Excel and CSV (ZIP Codes, IDs)

You open a customer file and the ZIP code 02134 now reads 2134. An employee ID 000457 reads 457. Nothing is wrong with the file: Excel simply treats anything that looks like a number as a number, and numbers have no leading zeros. This guide shows how to get them back and how to stop it from happening again.

Why Excel removes leading zeros

A cell in the default General format that contains 02134 is stored as the number 2134. Excel shows the number, not the typing. It happens with ZIP codes, product and barcode numbers, employee and account IDs, and phone numbers that start with 0.

The zeros are usually still in the CSV text file you were sent. They disappear when Excel opens the file and you save it again. So the safest habit is to keep the original file untouched and work on a copy.

Fix 1: add the zeros back with a formula

If every code should have the same length, rebuild it with TEXT. For a 5-digit ZIP code:

=TEXT(A2,"00000")

Use as many zeros as the final length. If some values may already be longer than the length you want, this version never cuts anything off:

=REPT("0",MAX(0,5-LEN(A2)))&A2

Fill the formula down, copy the results and use Paste Special, Values over the original column. The cells are now text, which is what you want. Excel may show a small green triangle ("number stored as text"); it is safe to ignore.

Fix 2: a number format, for display only

Select the column, press Ctrl+1, choose Custom and type 00000. The cell still holds the number 2134, but it displays as 02134. This is quick for viewing and printing. Because the underlying value is still a number, check how other tools see it: save a copy as CSV and open it in Notepad to see what was written.

Fix 3: stop it when you open a CSV

Double-clicking a .csv file makes Excel guess every column type, and the guess for 02134 is "number". Import it instead:

1. Open a blank workbook and choose Data, Get Data, From File, From Text/CSV.
2. Pick the file and click Transform Data.
3. Click the header of the affected column, then Data Type, then Text. Repeat for every column that holds codes.
4. Choose Close and Load.

In Google Sheets, use File, Import and untick "Convert text to numbers, dates, and formulas".

Fix 4: when typing or pasting

• Format the column as Text first (Home, Number format box, Text), then type or paste.
• Or type an apostrophe before the number: '02134. The apostrophe is not stored in the cell.

The 15-digit limit

Excel keeps only 15 significant digits in a number. In a 16-digit ID, everything after the 15th digit silently becomes a zero, and Excel often displays the number as 1.23457E+15. Those digits cannot be recovered afterwards. Long identifiers must be text from the moment they are pasted or imported.

Which fix for which data

Check for damage before you use the file

To count values that are shorter than a 5-digit code should be, which may have lost zeros:

=SUMPRODUCT((LEN(A2:A5000)>0)*(LEN(A2:A5000)<5))

A result above zero means some codes need a second look.

A whole file at once

DecodingPro reads each cell as it is stored and lists every change it makes, so a text value such as 02134 is never turned into a number, and nothing is changed without appearing in the log.

Questions people ask