**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 t**he 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 t**he 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!

So both methods worked, but how do I delete the previous numbers that I dont need anymore?

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.

we want to convert alphabet to number e.g

AA=0101

WA=2301

PW=1623