Using a custom number format to display leading zeros

This article was previously published under Q81518
SUMMARY
In Microsoft Excel, you can use either of two methods for using a custom number format to display leading zeros. Use one method for fixed-lengthnumbers and use the other method for variable-length numbers.

Method 1: Fixed-length Numbers

When you want to display leading zeros for a fixed-length number,create a custom format with the same number of zeros (0) asdigits that you want to display. For example, if you want todisplay a fixed-length number with five digits, create a customnumber format with five zeros.

Example 1:

Use the 00000 format to display a number with five digits. When you usethis format, the numbers that you type and the numbers that Microsoft Exceldisplays are listed in the following table.
   Typed number    Displayed number   --------------------------------   28217           28217   1234            01234				
Example 2:

Use the 00000-0000 format when you want the number to appear as five digits followed by a hyphen and four more digits. When you use thisformat, the numbers that you type and the numbers that Microsoft Exceldisplays are listed in the following table.
   Typed number    Displayed number   --------------------------------   8217            00000-8217   82170000        08217-0000

Method 2: Variable-length Numbers

When you want to display leading zeros for variable-lengthnumbers, create a custom format with the same number of zeros (0)(in quotation marks) as leading zeros that you want to display, followedby the number sign (#). For example, if you want to format avariable-length number with three leading zeros, create thefollowing custom number format:
"000"#
Example 1:

Use the "0"# format when you want to display one leading zero. When you usethis format, the numbers that you type and the numbers that Microsoft Exceldisplays are listed in the following table.
   Typed number    Displayed number   --------------------------------   123             0123   45              045				
Example 2:

Use the "000"# format when you want to display three leadingzeros. When you use this format, the numbers that you type and the numbersthat Excel displays are listed in the following table.
   Typed number    Displayed number   --------------------------------   123             000123   45              00045				
MORE INFORMATION
To create a custom number format, follow these steps:
  1. On the Format menu, click Cells.
  2. Click the Number tab.
  3. Click Custom.
  4. In the Type box, type the custom format.
  5. Click OK.
xl97 xl98 XL2000 xl2001 xl2002
Properties

Article ID: 81518 - Last Review: 09/19/2011 00:15:00 - Revision: 4.0

Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft Excel 2001 for Mac, Microsoft Excel 98 for Macintosh

  • kbhowto KB81518
Feedback