Pinterest Pixel

Add a Leading Zero in Excel

Bryan
Many times you may have faced a situation where you need to add a leading zero in Excel.
Add a Leading Zero in Excel | MyExcelOnline Add a Leading Zero in Excel | MyExcelOnline

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:

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

Add a Leading Zero in Excel | MyExcelOnline

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

download excel workbook Add-Leading-Zeros.xlsx

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”.

Add a Leading Zero in Excel

Step 2: Go to Home Tab > Number Group.

Add a Leading Zero in Excel

Step 3: From the dropdown select “Text”.

Add a Leading Zero in Excel

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

Add a Leading Zero in Excel

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

Add a Leading Zero in Excel

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

Add a Leading Zero in Excel

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.

Add a Leading Zero in Excel

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(

Add Leading Zeros in Excel

 

STEP 2: The TEXT arguments:

value

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

=TEXT(B9,

Add Leading Zeros in Excel

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”)

Add Leading Zeros in Excel

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

Add Leading Zeros in Excel

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

Add a Leading Zero in Excel

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

Add a Leading Zero in Excel

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

Add a Leading Zero in Excel

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.

Add a Leading Zero in Excel

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:

Add a Leading Zero in Excel | MyExcelOnline

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!

If you like this Excel tip, please share it
Add a Leading Zero in Excel | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  MEDIAN Formula in Excel

Steps To Follow

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...