XL: Using a Custom Number Format to Display Leading Zeros

ID: Q81518

The information in this article applies to:

SUMMARY

You can use either of two methods for using a custom number format in Microsoft Excel to display leading zeros. Use one method for fixed-length numbers 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) as digits that you want to display. For example, if you want to display a fixed-length number with five digits, create a custom number format with five zeros.

Example 1:

Use the 00000 format to display a number with five digits. When you use this format, the numbers that you type and the numbers that Microsoft Excel displays 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 a five digits followed by a hyphen and four more digits. When you use this format, the numbers that you type and the numbers that Microsoft Excel displays 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-length numbers, create a custom format with the same number of zeros (0) (in quotation marks) as leading zeros that you want to display, followed by the number sign (#). For example, if you want to format a variable-length number with three leading zeros, create the following custom number format: 


   "000"#


Example 1:

Use the "0"# format when you want to display one leading zero. When you use this format, the numbers that you type and the numbers that Microsoft Excel displays 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 leading zeros. When you use this format, the numbers that you type and the numbers that Microsoft Excel displays are listed in the following table.

Typed number Displayed number


123             000123
45              00045


MORE INFORMATION

To create a custom number format, use the following appropriate method.

Method 1: Microsoft Excel Versions 5.0 and Later

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 (or Code box in Excel 5.0), type the custom format.


  5. Click OK.


Microsoft Excel Versions 4.0 and Earlier

To create a custom number format, follow these steps:

  1. On the Format menu, click Number.


  2. Type the custom format in the Code box.


  3. Click OK.



REFERENCES

"Getting Results with Microsoft Excel for Windows 95," page 36

"User's Guide," version 5.0, pages 212-214

"User's Guide 1," version 4.0, pages 220-221

"User's Guide," version 3.0, pages 186-194

"Reference Guide," version 2.1, pages 321-326

Additional query words: 5.00c 2.10 2.10c 2.10d 2.20 2.21 2.1 2.1c 2.1d 2.2 3.0 7.00a 


Keywords          : xlformat 
Version           : WINDOWS:2.0,3.0,4.0,4.0a,5.0,5.0c,7.0,7.0a; MACINTOSH:2.0,3.0,4.0,5.0,98;os/2:2.2,3.0
Platform          : MACINTOSH OS/2 WINDOWS 
Issue type        : kbinfo

Last Reviewed: March 24, 1999