How to Add Leading Zeroes in Excel

There are situations when you need to add leading zeroes to a dataset in Excel. This could be the case if you maintain records in Excel, such as Employee Ids or Transaction Ids.

For example, you may want to get a consistent look in your data set as shown below:

Add Leading Zeroes in Excel - Transaction Ids

In this tutorial, you’ll learn various ways to add leading zeroes in Excel:

  • Converting the Format to Text
  • Using Custom Number Formatting
  • Using Text Function
  • Using REPT/LEN functions
  • Using VBA

Each of these methods has some merits and drawbacks (covered in each section).

Let’s see how each of these work.

Add Leading Zeroes by Converting the Format to Text

When to Use: When you have a small numeric data set, and you plan to do this editing manually.

Suppose you have employee Ids of the marketing department as shown below and you want to make these Ids look consistent by adding leading zeroes.

how-to-add-leading-zeroes-in-excel-employee-ids

So you try and change the id by entering leading zeroes (00001 instead of 1).

But to your amazement, Excel converts it back to 1.

Add Leading Zeroes in Excel Removes Zeroes

This happens as Excel understands that 00001 and 1 are the same numbers and should follow the same display rules.

Now as frustrating as it may be for you, Excel has its reasons.

So, to get the work done without bending Excel rules, you’ll have to take advantage of the fact that this rule doesn’t apply to text formatting.

So here is what you need to do:

  1. Select the cells in which you want to manually add leading zeroes.
  2. Go to Home → Number Group and select Text from the drop down.

Add Leading Zeroes in Excel - Select Number Formatting

That’s it!

Now, when you enter leading zeroes manually, Excel would readily comply.

Caution: When you convert the format to Text, some Excel functions will not work properly. For example, SUM/COUNT function would ignore the cell since it’s in the text format.

Add Leading Zeroes by Using Custom Number Formatting

When to Use: When you have a numeric dataset, and you want the result to be numeric (not text).

When you display a number in a specific format, it doesn’t change the underlying value of the number. For example, I can display the number 1000 as 1000 or 1,000 or 1000.00 or 001000 or 26-09-1902 (even dates are numbers in the backend in Excel).

In all the different ways to display the number, the value of the number never changes. It’s only the way it’s displayed that is changed.

To add leading zeroes, we can format it to show it that way, while the underlying value would remain unchanged.

Here are the steps to use this technique to add leading zeroes in Excel:

  1. Select the cells in which you want to add leading zeroes.
  2. Go to Home → Number Group and click on the dialog launcher (a small tilted arrow in the bottom right). This will open the Format Cells dialog box. Alternatively, you can also use the keyboard shortcut: Control + 1.Add Leading Zeroes in Excel - Dialog Box Launcher
  3. In the Format Cells dialog box, within the Number tab, select Custom in the Category list.Add Leading Zeroes in Excel - Format Cells
  4. In the Type field, enter 00000Add Leading Zeroes in Excel - Format
  5. Click OK.

Doing this will always display all the numbers as five digits, where leading 0’s are automatically added if the number is less than 5 digits. So 10 would become 00010 and 100 would become 00100.

In this case, we have used six zeroes, but if your data has numbers with more digits, then you need to use the format accordingly.

Note: This technique would work only for a numeric dataset. In case you have employee ids like A1, A2, A3 and so on, then these are text and would not change when you apply the custom format as shown above.

Add Leading Zeroes by Using TEXT Function

When to Use: When you want the result to be text.

TEXT function allows you to change the value to the desired format.

For example, if you want 1 to be displayed as 001, you can use TEXT function for that.

However, remember that the TEXT function would change the format and make it TEXT. This means that when you make 1 as 001, Excel treats the new result as text with three characters (just like abc or xyz).

Here is how to add leading zeroes using TEXT function:

  1. If you have the numbers in column A (say from A2:A100), then select B2:B100 and enter the following formula:
    =TEXT(A2,”00000″)
  2. Press Control + Enter to apply the formula to all the selected cells.

This will display all the numbers as five digits, where leading 0’s are automatically added if the number is less than 5 digits.

One benefit of converting data into text is that you can now use it in lookup formulas such as VLOOKUP or INDEX/MATCH to fetch the details of an employee using his/her employee id.

Note: This technique would work only for a numeric dataset. In case you have employee ids like A1, A2, A3 and so on, then these are text and would not change when you apply the custom format as shown above.

Add Leading Zeroes by Using REPT and LEN Functions

When to Use: When you have a data set that is numeric/alphanumeric, and you want the result to be text.

The drawback of using the TEXT function was that it would only work with numeric data. But in case you have an alphanumeric dataset (say A1, A2, A3 and so on), then TEXT function would fail.

In such cases, a combination of REPT and LEN function does the trick.

Here is how to do it:

  1. If you have the numbers in column A (say from A2:A100), then select B2:B100 and enter the following formula:
    =REPT(0,5-LEN(A2))&A2
  2. Press Control + Enter to apply the formula to all the selected cells.

This would make all the numbers/strings 5 characters long with leading zeroes wherever needed.

Here is how this formula works:

  • LEN(A2) gives the length of the string/numbers in the cell.
  • =REPT(0,5-LEN(A2)) would give the number of 0 that should be added. Here I have used 5 in the formula as that was the maximum length of string/numbers in my dataset. You can change this according to your data.
  •  =REPT(0,5-LEN(A2))&A2 would simply add the number of zeroes to the value of the cell. For example, if the value in the cell is 123, this would return 00123.

Add Leading Zeroes by Using Custom Function (VBA)

If adding leading zeroes in Excel is something you are required to do quite often, using a custom function is a good idea.

Here is the VBA code that will create a simple function for adding leading zeroes:

'Code by Sumit Bansal from http://trumpexcel.com
Function AddLeadingZeroes(ref As Range, Length As Integer)
Dim i As Integer
Dim Result As String
Dim StrLen As Integer
StrLen = Len(ref)
For i = 1 To Length
If i <= StrLen Then
Result = Result & Mid(ref, i, 1)
Else
Result = "0" & Result
End If
Next i
AddLeadingZeroes = Result
End Function

Simply add this code to the module code window, and you’ll be able to use it just like any other worksheet function.

Or create an add-in for it and be able to share it with your colleagues.

You May Also Like the Following Excel Tutorials:

  • Kevin says:

    I cannot get the leading zeros to stick when I save a sheet using VBA.
    I have a sheet called “Input” and in the W5:Wn (“W5:W” & LastRow) are numbers. I want all of them to be 3-digits with leading zeros as needed and then copy that column to “TimeSheet” worksheet in the same workbook, rows B2:Bn (“B2:B” & LastRow – 3). But the leading zeros vanish when the “TimeSheet” is saved as a separate, stand-alone workbook.

    I have tried a wide variety of approaches and nothing works. HELP, please.

  • Pablo Baez says:

    Hello Sumit,
    I find your content useful and easy to read. Thanks for sharing your knowledge.

    The TEXT function works the best for numbers, but not for actual text, for example if I get a part number like QJE and it needs to be 00QJE, Text doesn’t work.
    For the QJE example, =REPT(0,5-LEN(A2))&A2 works, but in my experience sometimes the part number is longer than 5 characters, in which case the formula fails (#VALUE! error). Unusual, but possible.

    So the solution is to use CONCATENATE, like this: =RIGHT(CONCATENATE(“00000”,A2),5), using as many zeros as the total expected length of the part number. This provides full flexibility and it handles and corrects part numbers than are anywhere from 1 to 5 or more characters.
    Using “&” instead of the CONCATENATE function also works: =RIGHT(“00000″&A2,5) and makes the formula shorter.

    Thanks,
    Pablo

  • >