How to Add Leading Zeros to Numbers or Text with Uneven Lengths

Bottom line: Learn how to write a simple formula to add zeros before numbers or text in a column where the values are not the same length. This is also known as padding the numbers with leading or preceding zeros.

Skill level: Easy

How to add leading zeros padding to a list of uneven length values in Excel

The Problem: How do we add the zeros back to the front of the number?

Megan asked a great question about how to add the zeros back to the beginning of some ID numbers.  This is often referred to as padding the numbers with zeros.

She has a list of employee IDs that were once text with leading zeros.  However, the text values got converted to numbers and the zeros at the beginning of each number were removed.

Add the leading padding zeros back to do lookup to numbers stored as text

We need to add the zeros back so we can do some lookups to another table.  That table contains a column of the ID numbers stored as text with the leading zeros.

Solution #1: Padding Numbers with the TEXT function

In this example the numbers in column A are different lengths, and we need to convert them to 6 digit numbers.

There are several ways to solve this problem, and using the TEXT function is probably the easiest.

The following image shows how to write the TEXT formula to convert the value to a string that is 6 characters long.

TEXT function to add leading zeros - padding - to numbers

=TEXT(A2, "000000")

The “000000” is the number format that will convert the number to a 6 digit number.  The zeros in the number format are placeholders for numbers.  If a number greater than zero exists then that number will be displayed, otherwise a zero will be displayed.

So, this simple formula will add the correct number of zeros to the front of the value to create a 6 digit number.  It is important to note that the formula returns a text value.  It looks like a number, but the data type is actually text.

This means we can use it in a lookup function like VLOOKUP or MATCH to lookup the employee ID in the data table and return the employee name.

The TEXT formula result for leading zeros is a text value that can be used in a vlookup formula

Solution #2: Padding Numbers or Text with the RIGHT function

Sometimes the numbers we need to pad also contain text.  In that case, the TEXT function will not work because it is applying a number format.

The RIGHT function can help us when the values contain numbers OR text.  It works for both and is a good universal solution.

Step 1: Add the Leading Zeros

The first step is to add the same number of leading zeros to the front of the number. In this example the shortest number in column A is 3 digits long. So we will add 3 zeros to the front of all numbers to make sure each number is at least 6 digits long.
Add leading zeros to the front of all cells in the column with uneven lengths
Some numbers will be longer than 6 digits, and that is ok for now.

Step 2: Use the RIGHT function to remove extra zeros

Now we can use the RIGHT function to return the 6 digits or characters on the right side of the value.
Use the RIGHT function to return the last 6 characters of numbers with leading zeros
This basically removes any extra zeros that we added to the front of numbers in step one.

Combine it into one simple formula to add leading zeros

I broke it into two steps to explain the formula. However, the steps can be combined into one simple formula.
Combine the leading zeros concatenation and RIGHT function into one formula
This simple formula will add the correct number of zeros to the front of the value to create a 6 digit number. It is important to note that the formula returns a text value. It looks like a number, but the data type is actually text.

Alternative solutions for padding numbers

Another alternative is to apply custom number formatting.  This will display the value as a 6 digit number.  However, it will NOT change the value.  The value in the cell will still be the original number that is less than 6 characters.  Therefore, we will NOT be able to use the formatted value in our lookup formula.

Custom Number Formatting to add leading zeros padding will NOT work in lookup formulas

We could also use a nested IF formula, the RIGHT function, or the CHOOSE function to accomplish the same task as the TEXT function.  All of those alternatives would be longer and more complex formulas.

How do you pad numbers or text?

Do you have a more complex scenario where you need to pad numbers?  We always do, right…? 🙂

Please leave a comment below with your scenario, or any questions or suggestions.  Thanks!


Your email address will not be published. Required fields are marked *

  • I have text representing bytes in Hexadecimal, but w/no leading 0 like:
    1, 9, A, B, 11, 19, 1C.

    I want to pad with a leading zero, like:
    01, 09, 0A, 11, 19, 1C.

    I tried: =TEXT(A1, “00”),
    but I still get no leading “0”… Probably because the input values are Alphanumeric, rather than Number.?.?.?

    Any help is appreciated

  • Hi i am building an online shop with a excel csv file for stock but hit a snag i am trying to be able to add 00 for the first 9 entry’s then 0 for the next 99 so it should show 00_name.jpg I can get 00 (=”00″) but unable to work out 00_name.jpg or 0_name.jpg as i have over 30k of entry’s life would be easy if some kind person was able to help with i am missing. Thank you in advance.

  • Column A contains alpha, alpha numeric values with different lengths. How to prefix zeros to make 6/7 digit values of some cells to 8 digits and at the same time retaining the 9 digits values in remaining cells.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter