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!

Please share
Jon Acampora

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 61 comments
usa attorney directory - January 1, 2019

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.

Ray Hartl - November 18, 2018

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:


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.

    Cleo Reavish - December 14, 2018

    kudos for this article

Nani - August 21, 2018

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?

    Tyler - August 23, 2018

    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

Zoaib - August 7, 2018

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

Glenda Boddie - July 20, 2018

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.

Henry Risoni - June 14, 2018

Dear Sir,

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




Cal - April 9, 2018

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,

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


‘ 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


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

' Not less than 12 – get next row
Next i

End Sub

KANBER CIRIT - April 2, 2018



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


Arun - February 28, 2018

Hi John,

I’m have one scenario

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

like this what all the possible way to do this

Thanks & regards

    Tyler - August 23, 2018

    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.

J - January 3, 2018


Rich - December 6, 2017

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



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

Any help would be appreciated


    John Atamanczyk - March 21, 2018

    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:


donna - November 28, 2017

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

Phurba - October 12, 2017

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)?


Alton - October 2, 2017

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

KISS - September 5, 2017

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)

Huma - September 4, 2017

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.


Mayra - August 28, 2017

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

zeeshan - July 2, 2017



Thanks & regard

Wes - June 21, 2017

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.


K Lilly - March 23, 2017

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!

Casey - February 25, 2017

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.

Melanie - February 25, 2017

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

Ryan - February 11, 2017

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

    Jon Acampora - February 15, 2017

    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.

Debbie - February 7, 2017

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.

    Jon Acampora - February 15, 2017

    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.


    You would then concatenate this result between cells B6 & C6


    I hope that helps.

Kristin - January 24, 2017

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?

    Jon Acampora - February 4, 2017

    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.

Linda - January 5, 2017

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?

    Jon Acampora - January 5, 2017

    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.

    Number Formatting for Zero Before Decimal

    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!

Ajay - November 10, 2016

Hi Jon

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

Example : 1234.56 to 12345 and then 000000000012345

    Jon Acampora - November 14, 2016

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

    =TEXT(A2*100, "000000")

    I hope that helps.

Faiz - July 28, 2016

Thanks for to learn easily to remember

Arturo López - June 9, 2016

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.

Deni - May 23, 2016

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

Pablo Baez - May 13, 2016

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:


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


    Jon Acampora - May 13, 2016

    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!

Awais - May 11, 2016

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

Chaminda - May 6, 2016

Hi Jon,
This is a great solution!


Kris - May 5, 2016

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

Mrs Squibbles - May 5, 2016

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.

    Jon Acampora - May 13, 2016

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

Carrie - May 5, 2016

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

Leonid - May 5, 2016

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.

    Jason - May 5, 2016

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

    Jon Acampora - May 13, 2016

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

Ammaar - May 5, 2016

That really smart and solves good amount of work around.


Matt - May 5, 2016

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:

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

    Jon Acampora - May 5, 2016

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

hocine - May 5, 2016

Thank you Jon for this article.

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

Have a nice day

    Jon Acampora - May 5, 2016

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

XLarium - May 5, 2016

Hello Jon, my approach would be:

    Jon Acampora - May 5, 2016

    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!

james - May 5, 2016

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

    Jon Acampora - May 5, 2016

    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!


Leave a Reply: