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

Thanks for another wonderful post. The place else may anybody get that type of information in such a perfect

method of writing? I’ve a presentation subsequent week, and I am on the search for

such information.

Hi. My problem is sorting a column of text based sku’s that have a number within the string.

For example: BJR23-12

In this instance the first 3 letters are based on the supplier but the next two characters (23) is a number that can vary from one to three characters. So for proper sorting there should be a zero before 23. What I have now when I sort is:

BJR213-7

BJR225-16

BJR23-12

BJR317-9

So adding a zero before 23 would move the sku to the top of the list where it should be. So I think I need a string function that looks at the characters to the left of the hyphen and if there are not six characters, adds one or two zeros after the first three characters.

Can you suggest a query expression to accomplish this? I keep trying with no success.

kudos for this article

Hi,

So here’s my issue.

I export tracking numbers and leading zeroes are missing. However, there are multiple carriers in the data and only one carrier uses leading zeroes. All of the tracking numbers that need zeroes start with eg: either 123, or 234. One set needs one zero, the other needs 2 zeroes. Any ideas?

You could try sorting with a text filter saying begins with 123 or 234 and use this formula to add the amount of 0’s to just those cells then undo the filter and you should have all the results you were expecting

Thank you Jon…it really save my time…looking forward to learn more from you.

Thanks again!

Thank you very much. I had forgotten how to add leading zeros using the “=TEXT(…” format. I appreciate the additional examples you provided and your comments regarding the resulting cell values.

Dear Sir,

I need help in adding zero at the end of number 2.

752.2

Thanks,

Henry

I would love to know if you could help me with a constant need for filtering a spreadsheet, where I need to add two zeros and I also deal with a character count issue.

So to explain, I have values that usually look like,

MV0099960000

MV010012520000

MV010019180000

MV0100200000

MV010020770000

MV0100210000

MV010027960000

I want them all to be 14 characters, the thing is I need to add the zeros after MV on the 12 characters strings.

So here is my thought process,

Remove/Replace MV

If 10 Characters add two leading zeros

If 12 Characters skip

Add MV to result

This is as far as I’ve gotten altering code I’ve found online. Any help appreciated! Thanks Jon, I’ll be in your webinar tomorrow at 10.

Sub if12charactersADDzeros()

‘REPLACE MV….

‘ Get rownumber of lastrow of data Col A

lastrow = Range(“a65536”).End(xlUp).Row

‘ Check row 1 to last row #, Col A

For i = 1 To lastrow

‘ If less than 12 chars

If Len(Cells(i, 1)) < 12 Then

'ADD TWO LEADING ZEROS

'RECHECK IF IT'S < 14 CHARACTERS

Cells(i, 1) = ………..

End If

' Not less than 12 – get next row

Next i

End Sub

A1=”ANKARA 1 NUMARALI DUKKAN”

A2=”ANKARA 2 NUMARALI DUKKAN”

..

A10=”ANKARA 10 NUMARALI DUKKAN”

…

A100=”ANKARA 100 NUMARALI DUKKAN”

how to change in A1 cell value to “ANKARA 001 NUMARALI DUKKAN”?

and A10 cell value “ANKARA 010 NUMARALI DUKKAN”

thanks

Hi John,

I’m have one scenario

example:

ID

12

300

1

1234

in this i like to add I and zero before the number like

I0012

I0300

I0001

I1234

like this what all the possible way to do this

Thanks & regards

Arun

One way I see doing this would be to follow the directions to get all the 0’s you need since they all seem to be 4 digits. Then I would create a column that has just the letter I or L that you need in front and copy that down the column by using the black box in the corner of the cell. Then use =CONCATENATE( to combine the cell with I/L and the number. Then I would copy and past the entire column as text so that the formulas are gone.

Thanks!!!!

Hi,

I have a list of share tickers ranging and I wish to add a “.” if the cell is only 2 letters long.

IE

AA

ADM

ANTO

based on this I would like to add a “.” infront of AA but leave the other cells

Any help would be appreciated

Thanks

Rich

You can use the LEN function to test for the number of characters in the cell. Then, nest that in an IF function to add the period to any 2-letter tickers, while allowing the ticker to remain the same for any other length. See the formula below:

=IF(LEN(A1)=2,”.”&A1,A1)

what if not all the numbers in the column are different lengths and not all need leading zeros?

Hi Jon,

I used this formula, “=$C2&”*”&$D2&”*”&$E2” where the referenced cell, “E2” has a leading zero. When the formula is applied to “Z2” the leading zero disappears and only the remaining numbers get copied. Any thoughts on copying the value as it appears in E2 (with the leading zero)?

Thanks,

Hi JON.

So this is what I am trying to do. I have 3 cells all numbers that I need to combine into one cell to make 1 account number.

A2 ( site ID = 3 digits)

G2 Account number (that has to be 9 digits which requires adding leading O’s) and

H2 the statement number (that has to be 3 digits which I need leading 0’s for some).

I tried this (“000”,B2)”&”(“000000000”,G2)”&”(“000”,H2) but didnt work because the o’s are not constant for my account #’s.

I can combine the cells with Concatenate but I still dont get the leading Zeros. ANy help would be great. Thanks 10/02/17

Good article. I’ve been doing similar for years and its good to share. With that in mind, I have often been asked to do this sort of thing via a macro. I used to insert a column, enter the formula, copy it down and delete the original column. Then I came across the Evaluate function. Here’s a sample code that will add the leading zeros in place without the need of a new column:

Sub AddLeadingZeros()

Dim wsMaster as Worksheet

Dim gNumbers as range

Set wsMaster = Activesheet

Set rgNumbers – wsMaster.Range(“A1:A10000”)

With rgNumbers

.NumberFormat = “@”

.value = Evaluate(“=””000000″” & ” & .address)

.value = Evaluate(“=IF(ISTEXT(” & .address & “),RIGHT(” & .address & “,6),””””)”)

End With

End Sub

The IF ISTEXT is just to force the formula to look at each cell in the address range.

Hope this helps someone.

KISS (Keep It Stupendously Simple)

Hi Jon,

I want excel sheet column to accept numbers with leading zero.

But not all the value will have leading zero.

Ex : 0123456789, 1234567892,987654870,00765865757.

How to achieve above scenario in excel sheet?

Please help.

Thanks

Huma

I need to a formula that take the information and format it to text, count the spaces up to a certain number and add zeros in order to complete that number of spaces. Example: the moon has 8 spaces I need to be 15 spaces that will be “0000000the moon” for a total of 15 spaces. Thank you

DEAR JON,

I JUST WANT TO ADD one ZERO (0) IN THE BEGINNING OF FORMAT LIKE ” 1234 – JANUARY SOLUTION” THIS SHOULD BECOME LIKE “01234 – JANUARY SOLUTION”. NEED YOUR GUIDANCE.

Thanks & regard

Hey Jon!

I need help. I have text cells with similar values but with inconsistent characters used. (M1-1, M1-01, M1-001) I need each cell to return any version of that entry and pad zeros after the hyphen to have a cell with 6 characters. So, M2-90 would return M2-090. You get where I’m going…. All the formulas I’ve tried aren’t working.

Thanks,

Wes

Hi Jon. I have taken over a spreadsheet that has repetitive data in it. One of my columns always has a number that starts with 1762A#####. Currently the number after the A is a zero. When I make a custom prefix, the zero is deleted. This happens when the 0 is added in the custom prefix and also when I manually enter it in. How do I format the cell to make the zero appear? Thanks!

How can I add leading dots to a table of contents in Excel 2013? I am required to do this for a school assignment and have been given zero direction. We are supposed to use the Repeat function and the length function to pad the cells.

How can I change the date format from 6/1/2017 to 06/01/2017. And what format must the excel be saved in.

I have a related issue which I though this would solve.

I have a form where users enter UK company registration numbers (format 01234567). Sometimes people omit the leading zero.

The data validation expects a cell entry with 8 characters and checks that they are all numbers.

To display the leading zero the cell is formatted as text.

However, the conditional formatting (changing cell background color when the registration number has been entered in the correct format) does not work when the cell is formatted as text.

I tried using a custom number format of 00000000 but this is not recognised by the data validation, even when you enter 01234567.

All of the elements work as long as the cell is not formatted as text – but then the number doesn’t display correctly!

I am stuck as all options seem to fix one part of the problem but not the other(s).

Hi Ryan,

For the data validation rule you can use a formula. Choose Custom in the Allow drop-down, then try the following formula.

Replace the A2 reference with the cell address of the cell that you are applying the validation to. Make sure it is a relative reference, no dollar signs, then you can copy it down.

You will also need to change the formatting of the cells to Text. I think that will work. Let me know.

What if I need to add zeros to the middle of a string? For example, need to create an account number using a concat formula but the resulting number must be 23 characters in length. I need to add zeros in the middle, or remove zeros to bring the length to 23. I have been doing this manually but there has to be a better way.

Hi Debbie,

Great question! You could use the LEN and RPT functions for that. If the first and last parts of the string were in cells B6 and C6, the following formula would count the number of characters in each cell, add them together, subtract that number from 23, and use the REPT (repeat) function to return the total number of zeros. Put the formula in cell D6.

=REPT(0,23-(LEN(B6)+LEN(C6)))

You would then concatenate this result between cells B6 & C6

=B6&D6&C6

I hope that helps.

Hi Jon,

I’m entering bank account numbers for employees and I want my excel spreadsheet to show the leading zeros, but not all bank account numbers are the same length. Can you tell me how to do that?

Hi Kristin,

You should be able to use step 1 of Solution #2 above to add a zero to the front of each cell. I hope that helps.

How do I put in a leading zero when it’s a text function in a formula when it is less than a dollar? Case in point my formula should bring back $0.23. But instead brings back $.23. It isn’t always less than a dollar but I want the zero in when it is less. I have tried forcing the zero in but it puts it in no matter how much and that isn’t good. I can’t find anything on this. Is there a way to do this?

Hi Linda,

Great question! That is a number formatting issue. You should be able to change the number formatting to see the zero before the decimal. The default Accounting format has this zero. On the Home tab of the ribbon, choose Accounting from the Number Format dropdown. Here is a screenshot.

You can also use the Currency format for this.

If we look at the Custom number format for the Currency format it looks like the following.

The zero before the decimal indicates that a zero appear if there is no value before the decimal. The # symbol indicates that the placeholder will be left blank if there is no value in it’s place.

I hope that helps. Let me know if you have any questions. Thanks!

Hi Jon

I want to convert a point decimal number to number then padding with zeros.

Example : 1234.56 to 12345 and then 000000000012345

Hi Ajay,

You could first multiple the number by 100, then use either of the solutions above.

I hope that helps.

Thanks for to learn easily to remember

Thanks Faiz!

Hi, Jon. What do you think about this alternative? =REPT(“0″,6-LEN(A1))&A1, in which A1 has 673. I do prefer the simplicity and it means that your solution with =TEXT(A1,”000000”) is the best one for me.

Best regards from Guadalajara, México.

Arturo L.

You could also just multiply the text column for 1 (cell*1), converting both to numbers without the worry of cracking your file

Hi Jon,

Great tutorial. Indeed TEXT is the best function when working with numbers, but what if you need to add the leading zeros to employee IDs or part numbers that are text? For example KOP should be 000KOP.

For actual text, the TEXT function doesn’t work, so I use this formula instead:

=RIGHT(“000000″&A2,6)

You pad it with as many zeros as the max length of the employee ID or part number.

Thanks,

Pablo

Thanks Pablo! I had actually originally written this article with that solution, then changed it to the TEXT function. I forgot that it only works with numbers, duh! 🙂

Good catch! I knew there was a reason I usually use the RIGHT function… Anyways, thanks for the suggestion. I will add a section back above with that solution as well.

Have a great weekend!

Thanks for updating, i need your kind help regarding amount convert to words. like 50,000 USD want to convert “Fifty Thousands Doller”.

Hi Awais,

You can use a macro (custom function). Here is an article that describes how to do it.

https://support.microsoft.com/en-us/kb/213360

Hi Jon,

This is a great solution!

Chaminda!

I always use function rept() instead of function text(). It looks easier. Thank you.

Wish I had seen this help sheet last week, would have saved me no end of time!! Ah well, at least I know what to do on my next spreadsheet. Thanks Jon for the tip and everyone else for their comments. Great help.

Thanks Mrs Squibbles! I’m happy to hear you will be saving time in the future. Sorry I was a week late… 😉

Another way to add a leading 0 is to have your text numbers or numbers in one column. Then in the next column concatenate them: = 0 & A1 or something like that. It works with words too (ex cell A1: “1Aa” cell A2: =0&A1 gives “01Aa” ). Probably won’t work very well if they are uneven lengths, in which case you could try using the LEN() with IF().

The using “000000” format in TEXT function works only for numbers. If content includes any letter(e.g. 1234A) the formula doesn’t work. I prefer to stay with the RIGHT(“000000″&A2,6) pattern. It’s 3 character longer than TEXT(A2,”000000”), but it’s universal.

I like that!

Good catch since identification or designation numbers I’ve seen can contain letters.

Universal.

Thanks Leonid! That is a great suggestion. I had originally published the post with that solution, then changed it to TEXT. I knew there was a reason I always use RIGHT, and forgot about numbers. I will add the section back above with the pics and explanation for the RIGHT function.

Thanks again! 🙂

That really smart and solves good amount of work around.

Kudos!!

Why not use the TEXT function e.g. =TEXT([Value],”00000″)

This will automatically pad the number to 5 digits using leading 0’s

For a more complex version try:

={TEXT([value],REPT(0,MAX(LEN([range]))))}

Use CTRL-Shift & Enter to make it an Array statement and this will automatically pad it out to the length of the longest figure

Hi Matt,

Great suggestions! I actually updated the post after publishing it with this solution as well. It is shorter and easier than the original solution I had with the RIGHT function. Thanks for sharing the array formula as well. Have a good one! 🙂

Thank you Jon for this article.

I think we can also use the TEXT() function. for example =TEXTE(A9;”000000″).

Have a nice day

Hi Hocine,

Yes, you are right. I realized the same thing not long after publishing the post and updated it with the TEXT function solution. Thanks for sharing your solution! 🙂

Hello Jon, my approach would be:

=TEXT(A2;”000000″)

Hi XLarium,

That is the correct approach. I realized that after publishing the post and updated it to explain the TEXT function. Thanks for the suggestion!

alternative I use is to format the Cell using Special and enter 000000 (using your example)

this way it knows it needs to be 6 characters long and will add any leading 0’s as needed

only used it a few times; but seems to work

Thanks for the suggestion James! The only issue with applying formatting is that it won’t work with the lookup formula because the stored number value does not change. I updated the post to explain that in more detail. I hope that helps. Let me know if you have any questions. Thanks again!