How to Keep Leading Zeros in Excel (ZIP Codes and IDs)
You type 02110 and Excel shows 2110. Excel treats ZIP codes and IDs like numbers, and a number does not need a zero at the start. Here are three ways to keep or add leading zeros. Start with Fix 1.
Restore ZIP code zeros in the whole sheet with the free tool
- Open the Fix Date and Number Formats tool.
- Choose your Excel file.
- Click Run and save the cleaned copy.
The tool puts the leading zero back on ZIP codes: 2110 becomes 02110. It also fixes dates and number formats. It never turns phone, ZIP or ID columns into numbers. Your original file stays as it is.
Show leading zeros with a custom format
- Select the cells.
- Press
Ctrl+1. - Choose Custom on the Number tab.
- Type
00000in the Type box and click OK.
The number 2110 now shows as 02110. The cell still holds the number 2110. Only the display changes. This is fine for ZIP codes, but not when you need real text, for example for an export.
Add leading zeros with the TEXT formula
Put this formula in an empty column and copy it down:
=TEXT(A2,"00000")
The number 2110 becomes the text 02110. Copy the new column and paste it back with Paste Special, Values. If your Excel wants a semicolon, use ; instead of the comma.
Leading zeros tips: typing, CSV files and long numbers
- Typing new numbers: type an apostrophe first, like
'02110. Excel keeps the zero. - New column: format the cells as Text before you paste or type. This works for new entries only.
- Long numbers: Excel keeps only 15 digits. Every digit after the 15th turns into a zero and cannot be restored. Use the original file.
Opening a CSV file? Do this first (leading zeros in CSV)
A double click on a CSV file makes Excel turn ZIP codes into numbers. The zeros are gone before you see the file. Import the file instead:
- Open a blank workbook in Excel.
- Go to Data, From Text/CSV and choose your file.
- Click Transform Data.
- Click the ZIP code column. Go to Transform, Data Type, Text.
- Click Close and Load.
Leading zeros in Excel: before and after
| Before | After | Method |
|---|---|---|
| 2110 | 02110 | Tool, custom format or TEXT |
| 8540 | 08540 | Tool, custom format or TEXT |
ZIP codes that already have five digits stay as they are. Only the short ones get their zero back.
Get your ZIP code zeros back in one click
The Fix Date and Number Formats tool runs on your own computer. Your file is never uploaded.
Questions about leading zeros in Excel
How do I add a zero in front of a number in Excel?
Use the custom format 00000 to show the zero, or the TEXT formula to create text like 02110. The free Fix Date and Number Formats tool does it for the whole sheet.
Why does Excel remove the zero from my ZIP code?
Excel reads ZIP codes as numbers, and a number has no use for a zero at the start. Format the cells as Text first, or fix them afterwards.
Why does Excel show 1.23E+15 instead of my number?
Long numbers switch to scientific notation. Format the cells as Text before you type or paste the numbers.
Can I get back digits after the 15th digit?
No. Excel changes every digit after the 15th to zero and does not store the original. Use the original file and import it with the column set to Text.
More Excel guides
How to Convert Text to Date in Excel (Dates Not Sorting)
Your dates do not sort? They are text. Here is how to turn them into real dates.
Mail Merge from Excel: How to Prepare Your List (No Errors)
Get your Excel list ready for a mail merge in Word.