Use leading zeros in postal codes and other numbers

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

  1. On the Format menu, click Cells.

  2. On the Number tab, click Custom.

  3. 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

  1. Click OK.

Display leading zeros in variable-length numbers

  1. On the Format menu, click Cells.

  2. On the Number tab, click Custom.

  3. 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

  1. Click OK.

See also

Display or hide zero values

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×