Apply leading zeros in Excel

Apply leading zeros in Excel

October 14, 2021 0 Door Bjorn Meijer

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.

Ignore error in cell
Ignore error in cell

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.
Leading Zeros in Excel Using Cell Properties
Adjust cell properties to Custom
a B C
1 Custom cell formatting Input Display
2 0000 123 00123
3 000# 123 0123
4 00-00 1 00-01
5 00-# 1 00-1
6 000-0000 123456 012-3456
7 ###-#### 123456 12-3456

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.

Leading Zeros in Excel Using an Apostrophe
Show leading zeros using an apostrophe

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.

a B C d E
1 Mobile number phone number Mobile number phone number
2 690548755 205676759 =TEXT(A2,"0000000000") =TEXT(B2,"0000000000")
3 654501233 204368755 =TEXT(A3,"0000000000") =TEXT(B3,"0000000000")
4 640192939 204236486 =TEXT(A4,"0000000000") =TEXT(B4,"0000000000")
5 651596496 203425697 =TEXT(A5,"0000000000") =TEXT(B5,"0000000000")
6 689245692 206345861 =TEXT(A6,"0000000000") =TEXT(B6,"0000000000")
7 645826571 2014765482 =TEXT(A7,"0000000000") =TEXT(B7,"0000000000")
8 642398534 206472387 =TEXT(A8,"0000000000") =TEXT(B8,"0000000000")
9 621569493 2097532166 =TEXT(A9,"0000000000") =TEXT(B9,"0000000000")
10 623147846 204357822 =TEXT(A10,"0000000000") =TEXT(B10,"0000000000")

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().

a B C
1 Number Number with leading zero
2 75489 =COMP(A2;"0")
3 215668 =SUMMIT(A3;"0")
4 1234 =COMP(A4;"0")
5 65443132134 =SUMMIT(A5,"0")
6 3491 =COMP(A6;"0")
7 653 =COMP(A7;"0")
8 87656 =COMP(A8;"0")
9 68723 =SUMMIT(A9;"0")
10 98764 =SUMMIT(A10,"0")

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.