How to Add Leading Zeros to Numbers or Text with Uneven Lengths - Excel Campus
45

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

Thanks,

Reply
Alton - October 2, 2017

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

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

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

Thanks
Huma

Reply
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

Reply
zeeshan - July 2, 2017

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

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

Thanks,
Wes

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

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

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

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

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

    =LEN(A2)=9

    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.

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

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

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

    You would then concatenate this result between cells B6 & C6

    =B6&D6&C6

    I hope that helps.

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

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

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

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

    $#,##0.00

    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!

    Reply
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

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

    Reply
Faiz - July 28, 2016

Thanks for to learn easily to remember

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

Reply
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

Reply
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:

=RIGHT(“000000″&A2,6)

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

Thanks,
Pablo

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

    Reply
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”.

Reply
Chaminda - May 6, 2016

Hi Jon,
This is a great solution!

Chaminda!

Reply
Kris - May 5, 2016

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

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

Reply
    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… 😉

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

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

Reply
    Jason - May 5, 2016

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

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

    Reply
Ammaar - May 5, 2016

That really smart and solves good amount of work around.

Kudos!!

Reply
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:
={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

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

    Reply
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

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

    Reply
XLarium - May 5, 2016

Hello Jon, my approach would be:
=TEXT(A2;”000000″)

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

    Reply
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

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

    Reply

Leave a Reply: