If you type a postal code such as 00345 into a cell, you will notice that the number is truncated to 345. Excel
provides two types of custom formats to display leading zeros: one for fixed-length numbers, such as phone numbers and postal codes, another for variable-length numbers.
Do any of the following:
 | Display leading zeros in fixed-length numbers |
-
On the Format menu, click Cells.
-
On the Number tab, click Custom.
-
In Type, type the same number of zeros as your fixed-length number.
Custom formats may include spaces and hyphens as well. For a set of phone numbers, you might type
000-0000.
Custom format | Typed number | Displayed number |
|---|
00000 |
00345 | 00345 |
0000 |
1234 | 1234 |
00-00 |
1 | 00-01 |
00000-0000 |
12340000 | 01234-0000 |
00 |
1 | 01 |
-
Click OK.
| Display leading zeros in variable-length numbers |
-
On the Format menu, click Cells.
-
On the Number tab, click Custom.
-
In Type, type the maximum number of leading zeros you want to display, followed by
#.
For example, if you create the custom format, 000#, then any number that you type into cells with that format will have up to three leading zeros.
Custom format | Typed number | Displayed number |
|---|
0# |
12 | 12 |
000# |
12 | 0012 |
00-# |
12 | 0-12 |
00000-000# |
1234 | 00001-234 |
.00# |
1 | 1.00 |
-
Click OK.
