Why Excel removes leading zeros, and how to keep them
You paste a list of customers and the ZIP code 02134 becomes 2134. A phone number turns into 3.33E+10. A 16-digit card or order number ends in 0 when it shouldn't. It's the same cause each time, and it can be avoided.
Why it happens
When a cell receives something made only of digits, Excel decides it's a number. A number has no leading zeros, so 02134 is stored as 2134. Two more rules make it worse:
- In the General format, long numbers are displayed in scientific notation: 33312345678 shows as 3.33E+10 when it doesn't fit the column, and from 12 digits up it always does.
- Excel keeps only 15 significant digits. From the 16th digit on, the rest becomes zeros, and that's not just display: the value is lost.
| What the page shows | What Excel keeps |
|---|---|
| ZIP code 02134 | 2134 |
| Invoice 000127 | 127 |
| Phone 33312345678 | 3.33E+10 |
| Order 4012888888881881 | 4012888888881880 |
For a code, these are not numbers: you never add up ZIP codes. They must stay text, exactly as written.
How to keep the zeros
Before pasting: make the column Text
- Select the columns that will hold codes.
- Right click › › OK.
- Paste with , so the Text format is kept.
It works, but you have to remember it before every paste, and choose the right columns: set the amounts to Text by mistake and they stop adding up.
When importing: set the column type
If the data comes from a CSV file or from , open and set the code columns to Text before loading. In the Text Import Wizard, the same choice is in the last step, under .
Switch off the conversion (Microsoft 365)
Recent versions of Excel (Microsoft 365 and Excel 2024) let you turn this behavior off: , untick Remove leading zeros and convert to number, and Keep the first 15 digits of long numbers and display in scientific notation if required. On a Mac: . It applies to everything you type and paste, so codes stay as they are; numbers that really are numbers are still recognized.
Already lost them? Rebuild with TEXT
If the column always has the same length (a 5-digit ZIP code), you can put the zeros back with a formula: =TEXT(A2,"00000"), with as many zeros as the length. Or use a custom number format 00000, which shows the zeros while the value stays a number. It doesn't help with digits lost after the 15th: those are gone.
Don't fix it with an apostrophe by hand. Typing '02134 works for one cell, not for 3,000 rows. And it doesn't help with a list you export again every week.
Or let the export do it
Export Button recognizes codes before writing the file: values with leading zeros and long digit sequences (phone numbers, tax IDs, order numbers) are written as text, exactly as on the page. In columns named ZIP, ID, Code, Invoice and the like, numbers are written without a thousands separator, so 10045 doesn't turn into 10,045. Amounts and quantities next to them stay real numbers you can add up. Nothing to set, column by column.
Export without losing a zero
Free for one table at a time, no account, and nothing leaves your browser.