Apply leading zeros in Excel

Introduction

Excel is a handy program that you can use not only for working with numbers, but also for keeping track of addresses or telephone numbers. Because Excel is a calculation program, all numbers are treated as numbers. So 001 sees Excel as the number 1. The leading zeros are automatically removed by Excel. Unless you explicitly state that this should not happen.

Within Excel you can work with leading zeros in different ways:

• Adjust the cell properties to Text;
• Adjust the cell properties to Amended;
• By placing a ' (apostrophe) in front of the leading zeros;
• With the TEXT();
• With the CONCATENATE();

Change cell properties to Text

Suppose you have an empty Excel sheet and you want to make a list of telephone numbers in it. You can do this as follows.

• Select the cells in which you want to type the phone numbers;
• Go to tab Start -> Group Number and select Text as number format.

As soon as you type a 0 for a number, Excel will show a green triangle in the upper left corner of the relevant cell. This implies that there is something wrong with the cell contents. To remove the triangle, select the cell(s) and then click the exclamation mark. Click in the menu on Ignore error.

Adjust cell properties to Custom

A second way to use leading zeros in Excel is to change the cell properties to Amended. This is useful for a list of numbers that must have the same number of characters.

• Select the cells which should contain leading zeros;
• Right-click on the selection and choose Cell properties from the context menu (or use the shortcut Ctrl+1);
• Choose in the tab Number for the Category Amended;
• Enter a format code of four zeros. The number of zeros indicates the number of characters in the cell;
• Click OK to save the changes.

As you can see in the example above, using custom number formats in Excel, you can add leading zeros to give numbers a fixed length. Depending on the designation in the format code, you can also use variable length leading zeros.

• 0 – displays additional zeros;
• # – does not display extra zeros.

Show leading zeros using an ' (apostrophe)

An easy way to display leading zeros is to put an ' (apostrophe) in front of the number to be entered. Similar to adjusting the cell properties to Amended a green triangle appears in Excel (the so-called error checking) in the top left corner of the cell. You can remove the green triangle by pressing Ignore error to click.

Display leading zeros using the TEXT() function

If you have a list of numbers and you want to give them the same length using leading numbers, you can use the function TEXT(). Excel then adds the number of leading zeros needed.

Display leading zeros using the SYMBOL() function

If not all numbers have the same length, but do you want to add a leading number everywhere? Then we can use the function PUT THE TEXT TOGETHER().

Another (perhaps simpler way) is to use the formula ="0" &A2.

Downloads

If you can't figure it out with the above manual, download the sample file.