How to Add Leading Zeros in Excel

This article will explain a few methods you can use to add leading zeros in Excel.

A leading zero refers to any 0 digit that comes before the first nonzero digit in a number string. 

Leading zeros are often used to make numbers have the same length. For example, we can add leading zeroes to 4, 25, and 99 so that they all have three digits. 

(“004”,”025”,”099”)

Let’s take a look at a quick example where we can add leading zeros to numbers in Excel.

Suppose we have a spreadsheet that keeps track of payments. We want to give each payment a six-digit number starting from “000001”. We already have a column with the payment number, but none of them is six digits long.

Is there a way to edit the values to add leading zeros automatically?

Excel offers custom number formatting that can be used to add leading zeros to a number. This is best used if you have a numerical dataset and want the final result to be numeric as well. 

Users can also use the TEXT function to change the numerical value to the desired format. This function, however, will output a text value rather than a numerical value. 

Leading zeros can also be added using a special formula that uses the REPT and LEN functions. We will later break down how this formula works and when best to use it.

Leading zeroes are often used to keep a column at a fixed length. Many legacy systems in the financial domain require values of a fixed length. Using the methods above will help adjust your data to fit any required length.  

Now that we know when to add leading zeros to numerical values, let’s take a look at some examples of how we can use this on an actual spreadsheet.

A Real Example of Adding Leading Zeros in Excel

Let’s take a look at some examples of using different methods being used to add leading zeros in an Excel spreadsheet.

The example below shows two columns with numerical data. The first column has the original numerical values, and the second column has the same values presented with leading zeros. This was achieved using custom formatting.

Besides using custom formatting, we can also use the TEXT function:

=TEXT(A4,”000000”)

If your values are alphanumeric, you can use an Excel formula to produce a similar format with leading zeros. In the example below, we added leading zeros to each alphanumeric value so that each string is 6 characters long.

To get the values in Column B, we just need to use the following formula:

=REPT(0,6-LEN(A4))&A4

We use the LEN function to find out how many leading zeros to add to the start of the string. In this example, we subtract the length of the original string from 6. Afterwards, we use the REPT function to repeat 0 the correct number of times. We use the “&” symbol to concatenate the leading zeros with the original value.

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try adding leading zeros in Excel, let’s learn how to write it ourselves!

How to Add Leading Zeros in Excel

This section will guide you through every step needed to add leading zeros in Excel. You’ll learn three different methods that you can use to add zeros at the start of your original values.

The first method involves using custom formatting options.

1. First, select the cell or cell range you want to add leading zeroes to. For this example, we’ll select range B4:B16.
   
   
2. Next, click on the icon seen below to open up the Format Cells dialog box.
   
   
3. Head over to the Custom category in the Format Cells Dialog box.
   
   
4. In the Type input box, type in a sequence of zeros where the amount of zeros equals to the length you desire for the final format. If you want a value with six digits, type “000000”.
   
   
5. Click on the OK button to apply the format to the selected range. Your values should now have leading zeros.
   
   

If you want your final value to be a text value, you can use the TEXT function.

1. First, select the cell you want to add to the TEXT formula. Afterward, type in “=TEXT(“ to start the function.
   
   
2. Next, we have to add our arguments to the TEXT function. The first argument will be the cell reference to the original value. The second argument will be the format to apply to the numerical value. In this case, we used “000000” to indicate that we want an output that’s six digit long.
   
   
3. Hit the Enter key to evaluate the function.
   
   
4. Drag down the formula in the first cell to fill out the rest of the column, as seen below.
   
   

For alphanumeric values, we can use a different Excel formula

1. First, select the cell we want to place our formula in. In this example, we’ll start by changing the value in cell A4 by adding a formula in cell B4.
   
   
2. Next, add the formula seen below. Replace A4 with your target cell and change the number 6 to the number of characters you want your final output to be.
   
   
3. Hit Enter to evaluate the formula. You can now use the same formula to add leading zeros to the rest of the column.
   

These are some of the easiest ways to start adding leading zeros in Excel. This step-by-step guide shows how easy it is to pad the beginning of a number or string to have uniform length strings.

You can now use the methods shown above and the various other Excel formulas available to create powerful worksheets that work for you.

Are you interested in learning more about what Excel can do? Stay notified of new guides like this by subscribing to our newsletter!

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe