Add a Leading Zero in Excel

Many times you may have faced a situation where you need to add a leading zero in Excel.

This tutorial will help you do exactly that! Do you have a lot of numbers with an uneven number of digits in your Excel list?

Do you want to make them uniform by adding leading zeros to them?

When adding zip codes, security numbers, or employee IDs in Excel, you may have seen that Excel removes any leading zeros in the cell. For example, if you type “007845” in Excel it will immediately turn it into “7845”.

This is because Excel automatically treats these values as numbers and tosses the zeros out.

These are various ways to add a leading zero in Excel. Let’s look at those options one by one:

• Change format to Text
• Add an apostrophe (‘)
• Use TEXT formula
• Use Custom Format

Watch this YouTube video to learn adding leading zeros in Excel and don’t forget to give it a thumbs-up!

Make sure to download the exercise workbook to follow along and learn how to add a leading zero in Excel.

Change format to Text

Since the reason why the zeros are tossed out is that Excel treats these values as numbers. The best option to add a leading zero in Excel would be to just change the format to the cell from “Number” to “Text”.

To change the cell format to text, follow the steps below:

Step 1: Select the cell in which you want to add prefix “0”.

Step 2: Go to Home Tab > Number Group.

Step 3: From the dropdown select “Text”.

Now when you add the zeros in front of the number, the zeros will remain intact.

You might notice a small yellow triangle on that cell, it is simply indicated that you have stored a number to text.

To remove that message, click on the triangle, and from the list, select “Ignore Error”.

As you can see, this method adds zeros in front of the number by changing the cell format to Number.

Add an apostrophe (‘)

You can simply add an apostrophe (‘) in front of the number to make sure that the zeros are in place. So, you can type ‘000001 instead of just 000001. In this way, the number will be shown as you want it to without having to change the format.

You can see in the formula bar that an apostrophe is added as a prefix to the number.

Use TEXT formula

Even though the above options gets the work done, it’s a pain to add zeros in front of them one by one!

Follow the steps below to understand how to add a leading zero with one single formula!

STEP 1: We need to enter the TEXT function in a blank cell:

=TEXT(

 

STEP 2: The TEXT arguments:

value

What is the value that you want to add a leading zero in Excel on?

=TEXT(B9,

format_text

How many leading zeros do you need?

If we want our number to be 6 digits long, then type in 6 zeros: “000000”

=TEXT(B9, “000000”)

Apply the same formula to the rest of the cells by dragging the lower right corner downwards. Your leading zeros are now ready!

You should keep in mind that all of these methods add a leading zero in Excel by actually converting the cell format to text one way or the other.

These methods will add the zeros in front of the numbers but the resulting value will be a text string, not a number.

You will not be able to use them to do any calculations with them or use them in numeric formulas.

This brings us to the last method to add leading zeros in Excel – Using Custom Format

Use Custom Format

You can add leading zeros in Excel by using a custom format. This will only change the display and not the value of the cell i.e. the value in the cell will still be a number but the display will contain leading zeros.

Let’s see how it can be done!

STEP 1: Select the column in which you want to add leading zeros

STEP 2: Press Ctrl +1 to open the Format Cells dialog box

STEP 3: Select Custom and under type section: type 000000. Click OK.

Leading zeros will be added to all the numbers. If you select a cell containing these numbers and look at the formula bar, you will see that the underlying value in the formula bar remains unchanged.

So, using this method you will display all 6 digits and the leading zeros will automatically be added to numbers containing less than 6 digits. Also, the value will still be a number and will not be converted to a text string.

Conclusion

In this tutorial, you have learned how to add leading zeros in Excel in more than just one way – changing number format to text, adding an apostrophe, using a TEXT formula, and using Custom Format.

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

You can learn more about how to use Excel by viewing our FREE Excel webinar training on Formulas, Pivot Tables, and Macros & VBA!

You can follow our YouTube channel to learn more about How To Use Excel for Dummies!