Excel drops leading zeros because it reads 00742 as the number 742. To show the zeros, give the cells the custom number format 00000; to turn the number into text with the zeros, use =TEXT(A2,"00000").
| A | B | C | |
|---|---|---|---|
| 1 | Number | TEXT(A2,"00000") | Format 00000 |
| 2 | 742 | 00742 | 00742 |
| 3 | 15 | 00015 | 00015 |
| 4 | 31415 | 31415 | 31415 |
Both columns look the same, but they are not: column B holds text, column C still holds the numbers 742, 15 and 31415 and only displays them with five digits. Each 0 in the code is a digit that is always shown, so 00000 means "at least five digits".
Keep leading zeros while typing
Three ways to type a zero-led code and keep it:
- Custom format. Select the cells, press Ctrl+1 (Cmd+1 on a Mac), choose Custom and type
00000in Type. The cells stay numbers, so SUM and sorting work. Use it for numbers of a fixed length, such as ZIP codes and employee numbers. - Apostrophe. Type
'00742. The apostrophe is not shown; it tells Excel to store the entry as text. - Text format. Select the cells, then Home > Number Format > Text, and type. Everything typed later is stored as text, exactly as typed.
| A | B | C | |
|---|---|---|---|
| 1 | Typed | Length | Is it text? |
| 2 | 00742 | 5 | TRUE |
| 3 | 742 | 3 | FALSE |
A2 was typed as '00742 and keeps all five characters. A3 was typed as 00742 without the apostrophe, so it became the number 742 and has 3 digits. Use text (apostrophe or Text format) for values that are codes rather than quantities: phone numbers, account numbers, part codes, anything longer than 15 digits (Excel keeps only 15 significant digits of a number, so a 16-digit card number typed as a number has its last digit turned into 0).
Add leading zeros with a formula
TEXT pads a number to a fixed number of digits. For a value that is already text, or may contain letters, put zeros in front and keep the last characters with RIGHT:
| A | B | C | |
|---|---|---|---|
| 1 | Value | TEXT(A2,"000000") | RIGHT("000000"&A2,6) |
| 2 | 42 | 000042 | 000042 |
| 3 | 1234 | 001234 | 001234 |
| 4 | A17 | A17 | 000A17 |
TEXT needs a number: it leaves the text A17 unchanged in B4. The RIGHT version works on both, because it joins six zeros in front of anything and then keeps the last six characters. RIGHT cuts a value that is already longer than six characters. =REPT("0",MAX(0,6-LEN(A2)))&A2 adds only the missing zeros and leaves a longer value whole.
| A | B | |
|---|---|---|
| 1 | Number | Employee ID |
| 2 | 4217 |
Your turn: Employee numbers have 6 digits. In B2, return the number in A2 as text with leading zeros, such as 004217.
Zeros inside a longer code
TEXT is also how you put a padded number inside a code or a sentence. Joining the number directly drops the zeros, even when the cell shows them with a custom format:
| A | B | C | |
|---|---|---|---|
| 1 | Order | With TEXT | Without TEXT |
| 2 | 0007 | ORD-0007 | ORD-7 |
| 3 | 0315 | ORD-0315 | ORD-315 |
| A | B | |
|---|---|---|
| 1 | Number | Invoice |
| 2 | 87 |
Your turn: In B2, build the invoice code INV- followed by the number in A2 padded to 5 digits, such as INV-00087.
Remove leading zeros, and the CSV trap
To turn text with leading zeros back into a number, use =VALUE(A2) (more on converting text to numbers). For a number shown with a 00000 format, set the format back to General.
The most common way to lose zeros is opening a CSV file: Excel converts 00742 to 742 as it opens the file, and saving the file again writes 742. To keep them:
- Open a blank workbook, then Data > From Text/CSV and pick the file.
- Click Transform Data, select the column, and set Data Type to Text.
- Click Close & Load.
In Microsoft 365, File > Options > Data > Automatic Data Conversion has a "Remove leading zeros and convert to a number" setting; clear it and Excel keeps the zeros when you type or open CSV files. In Google Sheets, format the column as Plain text before you type or paste the codes.
Frequently Asked Questions
Why does Excel remove leading zeros?
Excel reads 00742 as the number 742, and a number has no leading zeros. Format the cells as Text before typing, type an apostrophe first ('00742), or keep the number and give it the custom format 00000.
How do I add leading zeros in Excel with a formula?
=TEXT(A2,"00000") pads a number to 5 digits, so 742 becomes 00742. For text that may contain letters, use =RIGHT("00000"&A2,5).
How do I keep leading zeros when opening a CSV file?
Do not double-click the CSV. Use Data > From Text/CSV, click Transform Data, set the column's type to Text, and load it. In Microsoft 365 you can also turn off File > Options > Data > Automatic Data Conversion > Remove leading zeros.
How do I remove leading zeros in Excel?
If the cell holds text such as '00742, =VALUE(A2) returns the number 742. If it is a number with a 00000 format, change the format back to General.
