How to Add Zero in Front of Number in Excel

Many a time, you may have a long list of numbers in Excel and you may wish to add zero before the number. Do you know how can you achieve it? In this blog, we would look at multiple ways to add or insert one or more zero in front of a number or text in Microsoft excel.

However, before starting with the different methods to insert or add 0 in front of a number, let us do one small activity.

Small Activity to Brush Up

Suppose, you have a list of numbers in excel worksheet like the way shown in the image below:

Firstly, let us try to manually add one or more zeros before the numbers. To do so, click on the cell A2 and press F2 on your keyboard to open the cell in edit mode. Now, try entering the leading zero (0) before the first number as shown in the screenshot below, and press the “Enter” key.

You would notice that the zeros entered before the number gets vanished and you have same number again.

Then, how can we add zero in front of number in Excel? This is what we are going to learn in the coming sections of this blog.

Click on the Download button below to download the sample file for practice.

Ways to Add Zero In Front of Number in Excel

There are multiple ways using which we can insert or add leading zero before a number.

1. By Adding Single Apostrophe Before Zeros
2. Using the TEXT Formula
3. Using the Custom Number Formatting

Let us understand each of these methods in detail with it’s own advantages and dis-advantages.

Add Zero in Front of Number Using Single Apostrophe

This is the easiest method to add leading zeros before the numbers in excel. Follow the undermentioned steps to achieve the same:

Select the cell and press the “F2” key on your keyboard to open the cell in an Edit mode. Place your cursor at the beginning.

Then, enter the apostrophe (‘) symbol on your keyboard followed by the zeros. And press Enter key.

As a result, you would notice that now the number in cell A2 has leading zeros before it.

Perform the above steps with all the numbers from cell A3 to A8 to add leading zeros.

Limitation of Using This Method

There are two major limitations of this method:

• You cannot copy the formatting and paste it to other numbers. Zeros need to be added manually for each number individually one by one.
• Once ‘000 is added before the numbers, it is then a normal text for excel. The number loses its number characteristic. Zeros need to be added manually for each number individually one by one.

Now let us check on the next method.

Add Zeros Before Number Using TEXT Formula

In this method, we see a formula to add zero before a number in excel.

It is a bit quicker method as compared to the previous one with an added advantage of copying the formula to other cells and therefore allowing to quickly add the zero before each number.

Enter the following formula in cell B2: =TEXT(A2,”000000″)

This formula would convert the number into six character length number as shown in the screenshot below:

Copy this formula and paste it in the other cells in column B.

Limitation of Using This Method

Just like the first method, this method also comes with a limitation. The TEXT formula converts the cell value into the desired text format. This means that you cannot perform any mathematical operation with these numbers as the numbers lose their number characteristics.

Add Zero In Front of Number – Custom Number Formatting

The custom number formatting feature in the Format Cell dialog box allows us to create our own format for numbers, texts, dates, time, etc. It only changes the display of the numbers in a cell without changing its original properties or characteristics.

This is considered as the best method for inserting leading zero before a number in an excel worksheet as it would take care of the disadvantages of the other two methods learned above.

You can easily perform the mathematical operation with the resultant numbers. There is no change in the characteristics of the numbers into plain text. To achieve it, follow the undermentioned steps-

As a result, you would notice that the numbers in the selected cell get converted into a six-digit number as shown in the screenshot below :

Most importantly, the characteristics of these numbers did not change. You can use these just like a number.

With this, we have reached the end of this blog. Share your views and comments in the section below.