Computer and Software Support


Keep leading zero in number

 

Excel

There is two ways to show the leading zero. Click the tip you need assistance with

 

Keep all leading zeros

Thank you Paul for this tip

 

If you always want a zero or a number of zeros to show up before the number, this procedure will show you how.

 

When you type

   

Excel cuts off the zero so it looks like this

   

If you want the leading zero to show, use the apostrophe

   

Place the apostrophe before the zero

   

Now the leading zero will show

 

The difference between using the apostraphe and using format is as follows

  1. Using apostrophe, All leading zeros will show. Example

    1. If I typed '033, Excel will show 033

    2. If I typed '0333, Excel will show 0333

    3. If I typed '0033, Excel will show 0033

    4. No matter what number you type, the number of zeros you typed will always show

  2. This is different from using Format. Format will only show the zero if the placeholder is under 9. See Use Format for more information.

 

Use Format

This feature will allow you to show the leading zero (ifIf you need to show the leading zero but limit how high of a number that you can use, use the following

This tip is from elijournels and pasted here to prevent broken links and guarantee that you have access to the information. To see the information on that site click here (I cannot guarantee the link is still valid)

When your in Excel and try to enter a number such as 0244, Excel cuts off the 0 and only shows 244. If you need the number to show than do the following

  1. Open Excel
  2. Select the cell or cells that you want the leading zeros to show
   
  1. Right-click the cell and click Format Cells dialog box
   
  1. Select Custom from the Category list box
   
  1. In the Type text box, enter the number of 00000 that is the same number that you are entering. For example,
    1. If you want to show 02 through 09- enter 00
      1. It will show a leading zero up to 09. The max number you can type is 99
    2. If you want to show 002 through 099 - enter 000
      1. It will show a leading zero up to 099. The max number you can type is 999
    3. If you want to show 0002 through 0999 - enter 0000
      1. It will show a leading zero up to 0999. The max number you can type is 9999
    4. and so on
   
  1. Click OK to apply the change
 

Note: with this option, The zero will only appear, if it is under a specific number. If you set format to 000, then it will only show the zero up to 099. When you type 100 there will be no leading zero. However you are not limited to how large the number can be. You can type 455022 if you want. However there will not be a leading zero. If you always want a leading zero to show, use Keep all leading zeros.