You’ll find that if you type a number beginning with zero (a leading zero) into an Excel cell, Excel will usually remove the zero.  Most of the time, we don’t need zeros at the beginning of numbers.  But sometimes leading zeroes are very important.  After all, which spy sound more impressive, James Bond, 7, or James Bond, 007?

There are lots of other reasons for leading zeroes, like product numbers, account IDs, and zip codes just to name a few. 

Here are four options for showing leading zeroes:

1.  Format as Text*

If you format a cell as text, Excel will not remove leading zeroes.  This is best done before you enter the numbers. If you’ve already entered your data and the leading zeroes have been removed, try the TEXT function described further down in this post.

Excel 2007 & 2010

  1. Select all the cells in which you will enter data that might have leading zeroes.
  2. Click on the Home tab of the ribbon.
  3. In the Number section, click the drop down arrow on the Number Format box.  In 2010, select Text, and you’re finished.  In 2007, select More Number Formats and continue with steps 3 & 4.
  4. In the Category section of the dialog box that opens, select Text.
  5. Click OK.

Excel 2003

  1. Select the cells in which you will enter data that might have leading zeroes.
  2. Go to the Format menu and select Cells.
  3. Click on the Number tab.
  4. In the Category section, select Text.
  5. Click OK.

2.  Store a Number as Text by Adding an Apostrophe*

At the beginning of the number just type a ‘.  For example, if you want a cell to display 00957, type ’00957.  This will store the number as text. 

3.  Use the TEXT Function*

If you’ve already put the numbers into your spreadsheet and the zeroes have been removed, you can convert the numbers to text and specify the number of digits shown using the TEXT function.  This will format the information as text and add the leading zeroes. 

The syntax for the Text function is:

TEXT(value,format text)

Value is the value you want to convert to text, so it will be the cell reference that contains the number you want to have leading zeroes.

Format text is the formatting you want to apply to the number.  In the case of showing leading zeroes, you will open quotes, enter a zero like a place holder for each digit you want to display, and close quotes.

In cell D1, I have a number with a total of four digits, including the leading zeroes. In another cell I’ll enter the formula:

=TEXT(D1,”0000″) 

Don’t forget that you will need to Paste Values afterward, if you want to replace the original number with the function results.  Read about Paste Values in the second half of our Combine the Contents of Two Cells post.

For more information about the TEXT Function, read Microsoft’s TEXT Function article.

4.  Format as a Zip Code

If your number is a zip code, you can format the cell as a zip code. 

Excel 2007 & 2010

  1. Select cells.
  2. Click on the Home tab of the ribbon.
  3. In the Number section, click the drop down arrow on the Number Format box, and select More Number Formats.
  4. In the Category section, click on Special.
  5. In the Type box, select Zip Code.
  6. Click OK.

Excel 2003

  1. Select cells.
  2. Go to the Format menu and select Cells.
  3. Click on the Number tab.
  4. In the Category section, select Special.
  5. In the Type box, select Zip Code.
  6. Click OK.

If you’re going to do a mail merge with these zip codes, it’s a good idea to store them as text instead of zip codes.  Here’s an article about it  http://www.pcmag.com/article2/0,2817,2006904,00.asp.

*The Little Green Triangle 

You’ll notice that when a number is stored as text, a green triangle appears in the top left corner of the cell.  If you use the TEXT function, you won’t see the triangle unless you paste values).  If you hover over a cell with the triangle, an exclamation point will appear to the left.  If you click on this, it will tell you that you’re getting an error because the cell has a number stored as text.  To get rid of the triangle, all you have to do is click on the exclamation point and click on Ignore error from the menu that drops down.  You can select multiple cells and do this once.