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

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.

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

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.

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.

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.

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.

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!