How to add leading zeros in Excel

This tutorial shows different ways to add leading zeros in Excel: how to keep zeros as you type, show leading zeros in cells, remove or hide zeros.

If you use Excel not only to calculate numbers, but also maintain records such as zip codes, security numbers or employee ids, you may need to keep leading zeros in cells. However if you try typing a zip code like "00123" in a cell, Excel will immediately truncate it to "123".

The point is that Microsoft Excel treats postal codes, phone numbers and other similar entries as numbers, applies the General or Number format to them, and automatically removes preceding zeroes. Luckily, Excel also provides the means to keep leading zeros in cells, and further on in this tutorial you will find a handful of ways to do it.

How to keep leading zeros in Excel as you type

For starters, let's see how you can put 0 in front of a number in Excel, for example type 01 in a cell. For this, simply change the cell format to Text:

  • Select the cell(s) where you want to prefix numbers with 0.
  • Go to the Home tab > Number group, and select Text in the Number Format box.

As soon as you type a zero(s) before number, Excel will display a small green triangle in the top-left corner of the cell indicating that something is wrong with the cell contents. To remove that error indicator, select the cell(s), click the warning sign, and then click Ignore Error.
Type numbers with leading zeros in Excel.

The following screenshot shows the result:
Keep leading zeros in Excel.

Another way to keep leading zeros in Excel is to prefix a number with an apostrophe ('). For example, instead of typing 01, type '01. In this case, you don't need to change the cell's format.

Bottom line: This simple technique has a significant limitation - the resulting value is a text string, not number, and consequently it cannot be used in calculations and numeric formulas. If that is not what you want, then change only the visual representation of the value by applying a custom number format as demonstrated in the next example.

How to show leading zeroes in Excel with custom number format

To display leading zeroes, apply a custom number format by performing these steps:

  1. Select a cell(s) where you want to show leading zeros, and press Ctrl+1 to open the Format Cells dialog.
  2. Under Category, select Custom.
  3. Type a format code in the Type box.

    In most cases, you will need a format code consisting of 0 placeholders, like 00. The number of zeros in the format code corresponds to the total number of digits you want to show in a cell (you will find a few examples below).

  4. Click OK to save the changes.

For example, to add leading zeros to create a 5-digit number, use the following format code: 00000
Show leading zeroes by applying a custom number format.

By using Excel custom numbers formats, you can add leading zeros to create fixed-length numbers, like in the above example, and variable-length numbers. It all boils down to which placeholder you use in the format code:

  • 0 - displays extra zeros
  • # - does not display extra zeros

For example, if you apply the 000# format to some cell, any number you type in that cell will have up to 3 leading zeros.

Your custom number formats can also include spaces, hyphens, parentheses, etc. The detailed explanation can be found here: Custom Excel number format.

The following spreadsheet gives a few more examples of custom formats that can show leading zeros in Excel.

A B C
1 Custom format Typed number Displayed number
2 00000 123 00123
3 000# 123 0123
4 00-00 1 00-01
5 00-# 1 00-1
6 000-0000 123456 012-3456
7 ###-#### 123456 12-3456

And the following format codes can be used to display numbers in special formats such us zip codes, phone numbers, credit card numbers, and social security numbers.

A B C D
1   Custom format Typed number Displayed number
2 Zip code 00000 1234 01234
3 Social security 000-00-0000 12345678 012-34-5678
4 Credit card 0000-0000-0000-0000 12345556789123 0012-3455-5678-9123
5 Phone numbers 00-0-000-000-0000 12345556789 00-1-234-555-6789

Tip. Excel has a few predefined Special formats for postal codes, telephone numbers and social security numbers, as shown in the screenshot below:
Special formats for postal codes, telephone numbers and social security numbers

Bottom line: This method is best to be used in situations when you work with a numeric dataset and the results should be numbers, not text. It changes only the display of a number, but not the number itself: leading zeros show up in cells, the actual value displays in the formula bar. When you reference such cells in formulas, the calculations are perfumed with the original values. Custom formats can only be applied to numeric data (numbers and dates) and the result is also a number or date.

How to add leading zeros in Excel with the TEXT function

While a custom number format shows zero in front of a number without actually changing the underlying value, the Excel TEXT function pads numbers with zeros by "physically" inserting leading zeros in cells.

To add leading zeros with a TEXT(value, format_text) formula, you use the same format codes as in custom number formats. However, the result of the TEXT function is always a text string, even if it looks much like a number.

For example, to insert 0 before a value in cell A2, use this formula:

=TEXT(A2, "0#")

To create a zero-prefixed string of a fixed length, say a 5-character string, use this one:

=TEXT(A2, "000000")

Please pay attention that the TEXT function requires enclosing the format codes in quotation marks. And this is how the results will look like in Excel:

A B C
1 Original number Padded number Formula
2 1 01 =TEXT(B2, "0#")
3 12 12 =TEXT(B3, "0#")
4 1 00001 =TEXT(B4,"00000")
5 12 00012 =TEXT(B5,"00000")

For more information about Text formulas, please see How to use the TEXT function in Excel.

Bottom line: The Excel TEXT function always returns a text string, not number, and therefore you won't be able to use the results in arithmetic calculations and other formulas, unless you need to compare the output with other text strings.

How to add leading zeros to text strings

In the previous examples, you learned how to add zero before a number in Excel. But what if you need to put zero(s) in front of a text string like 0A102? In that case, neither TEXT nor custom format will work because they deal with numeric values only.

If the value to be padded with zero contains letters or other text characters, use one of the following formulas, which offer a universal solution applicable to both numbers and text strings.

Formula 1. Add leading zeros using the RIGHT function

The easiest way to put leading zeros before text strings in Excel is using the RIGHT function:

RIGHT("0000" & cell, string_length)

Where:

  • "0000" is the maximum number of zeros you want to add. For example, to add 2 zeros, you type "00".
  • Cell is a reference to the cell containing the original value.
  • String_length is how many characters the resulting string should contain.

For example, to make a zero-prefixed 6-character string based on a value in cell A2, use this formula:

=RIGHT("000000"&A2, 6)

What the formula does is add 6 zeros to the value in A2 ("000000"&A2), and then extract the right 6 characters. As the result, it inserts just the right number of zeros to reach the specified total string limit:
Add leading zeros using the RIGHT function.

In the above example, the maximum number of zeros equals the total string length (6 characters), and therefore all of the resulting strings are 6-character long (fixed length). If applied to a blank cell, the formula would return a string consisting of 6 zeros.

Depending on your business logic, you can supply different numbers of zeros and total characters, for example:

=RIGHT("00"&A2, 6)

As the result, you will get variable-length strings that contain up to 2 leading zeros:
Add leading zeros to create variable-length strings.

Formula 2. Pad leading zeros using the REPT and LEN functions

Another way to insert leading zeros before a text string in Excel is using this combination of REPT and LEN functions:

REPT(0, number of zeros-LEN(cell))&cell

For example, to add leading zeroes to the value in A2 to create a 6-character string, this formula goes as follows:

=REPT(0, 6-LEN(A2))&A2

How this formula works:

Knowing that the REPT function repeats a given character a specified number of times, and LEN returns the total length of the string, the formula's logic is easy to understand:

  • LEN(A2) gets the total number of characters in cell A2.
  • REPT(0, 6-LEN(A2)) adds the required number of zeros. To calculate how many zeros should be added, you subtract the length of the string in A2 from the maximum number of zeros.
  • Finally, you concatenate zeros with the A2 value, and get the following result:

Pad leading zeros using the REPT and LEN functions.

Bottom line: This formula can add leading zeros both to numbers and text strings, but the result is always text, not number.

How to add a fixed number of preceding zeros

To prefix all values in a column (numbers or text strings) with a certain number of zeros, use the CONCATENATE function, or the CONCAT function in Excel 365 - 2019, or the ampersand operator.

For example, to put 0 before a number in cell A2, use one of these formulas:

=CONCATENATE(0,A2)

or

=0&A2

As shown in the screenshot below, the formula adds just one leading zero to all cells in a column regardless of how many characters the original value contains:
Add a fixed number of preceding zeros in Excel.

In the same manner, you can insert 2 leading zeros (00), 3 zeros (000) or as many zeros as you want before numbers and text strings.

Bottom line: The result of this formula is also a text string even when you are concatenating zeros with numbers.

How to remove leading zeros in Excel

The method you use to remove leading zeros in Excel depends on how those zeros were added:

  • If preceding zeroes were added with a custom number format (zeros are visible in a cell, but not in the formula bar), apply another custom format or revert back General as shown here.
  • If zeros were typed or otherwise entered in cells formatted as Text (a small green triangle is displayed in the top-left corner of the cell), convert text to number.
  • If leading zeroes were added by using a formula (the formula appears in the formula bar when the cell is selected), use the VALUE function to remove them.

The following image shows all three cases to help you choose the right technique:
Different ways to add leading zeros in Excel

Remove leading zeros by changing the cell format

If leading zeroes are shown in cells with a custom format, then change the cell format back to default General, or apply another number format that does not display preceding zeros.
Remove leading zeros by applying the General format.

Remove leading zeros by converting text to number

When prefixed zeros appear in a Text-formatted cell, the easiest way to remove them is select the cell(s), click the exclamation point, and then click Convert to Number:
Remove leading zeros by converting text to number.

Remove leading zeros by using a formula

In case a preceding zero(s) is added with a formula, use another formula to remove it. The zero-removing formula is as simple as:

=VALUE(A2)

Where A2 is the cell from which you want to remove preceding zeros.

This method can also be used to get rid of zeros typed directly in cells (like in the previous example) or imported to Excel from some external source. Overall, if you are dealing with a zero-prefixed string that represents a number, you can use the VALUE function to convert text to number and remove leading zeros along the way.

The following screenshot shows two formulas:

  • The Text formula in B2 adds zeros to the value in A2, and
  • The Value formula in C2 removes the leading zeros from the value in B2.

Excel formula to remove leading zeros

How to hide zeros in Excel

If you don't want to display zero values in your Excel sheet, you have the following two options:

  1. To hide zeros across the entire sheet, uncheck the Show a zero in cells that have zero value option. For this, click File > Options > Advanced, and scroll down to the Display options for this worksheet section:
    Hide zero values across the entire sheet.
  2. To hide zero values in certain cells, apply the following custom number format to those cells: #;#;;@

For this, select the cells where you want to hide zeros, click Ctrl+1 to open the Format Cells dialog, select Custom under Category, and type the above format code in the Type box.

The screenshot below shows that cell B2 does contain a zero value, but it is not displayed in the cell:
Hide zero values in certain cells.

Add and remove zeros in Excel an easy way

Finally, good news for users of our Ultimate Suite for Excel - a new tool especially designed for handling zeros is released! Please welcome Add/Remove Leading Zeros.
Add/Remove Leading Zeros for Excel

As usual, we've strived to reduce the number of moves to an absolute minimum :)

To add leading zeros, here's what you do:

  1. Select the target cells and run the Add/Remove Leading Zeros tool.
  2. Specify the total number of characters that should be displayed.
  3. Click Apply.

Done!
Adding leading zeros

To remove leading zeros, the steps are very much alike:

  1. Select the cells with your numbers and run the add-in.
  2. Specify how many characters should be displayed. To get the maximum number of significant digits in the selected range, click the Get Max Length
  3. Click Apply.

The add-in can add leading zeros to both numbers and strings:

  • For numbers, a custom number format is set, i.e. only a visual representation of a number is changed, not the underlying value.
  • Alpha-numeric strings are prefixed with leading zeros, i.e. zeroes are physically inserted in cells.

This is how you can add, remove and hide zeros in Excel. To better understand the techniques described in this tutorial, you are welcome to download the sample workbook. I thank you for reading and hope to see you on our blog next week!

Available downloads

Excel Leading Zeros examples (.xlsx file)
Ultimate Suite 14-day fully-functional version (.exe file)

Table of contents

103 comments

  1. Awesome!

  2. Can someone help me? With this example "S-1-23" how can I add zero before the number 1?
    thank you guys!

  3. i want to number convert to zero but my formula not change

  4. Really a great post, appreciate your efforts

  5. Great post!

  6. How to add zero in front of order no Eg : 2759479

  7. Hello,

    I have IDs that need to be made uniform. I need them to go from B102, B22, B1444, etc to B0102, B0022, B1444, etc. Adding a leading zero to the number after the B.

    Can someone recommend a formula?

    Thank you in advanced for your help!

    Gabriella

  8. Hai,
    is there any way to automatically fill a cell with Zero when the content therein is deleted

    ie i have a range of raw cells filled with some data s of text and numbers. when i delete the data in one cell the same has to be automatically filled with zero and remain till a new data is entered
    Please provide a solution
    Thank you
    Anil

    • Hello!
      If you want 0 to be inserted after deleting a value in a cell, press 0 instead of the DEL key. Or, you must use a VBA macro.

  9. how to remove leading zeros in from of the numbers.
    Thank you.

  10. I am trying to concatenate cell A2 and cell B2.
    Cell A2 has a whole number, like 7.
    Cell B2 has a decimal, like 0.56
    I want the result to be 7.56
    Instead I get 7.056 (because cell B2 insists on formatting decimals as 0.00 no matter what I try. So that the 0 always gets caught after my whole number. I tried #;#;;@. Nothing works to remove that 0.
    Please help

    • Hello Marcy!
      If I understand your task correctly, the following formula should work for you:

      =IF(B2>1,A2&B2,A2&REPLACE(B2,1,1,""))

      I hope it’ll be helpful.

  11. I want to remove all 3 zeros infront of 7.
    0007204187

  12. thank you so much for your help.

  13. I am changing a username from an old Username to a new Username that has leading zeros. When I use this to insert into the page, it ends up truncating the leading zeros from the New Username. An example would be:
    Old Username New username
    smooth 003888 shows the new username as 3888. Here is what we are using:
    = "Update UsersALL Set Username = "&"'"& B1 &"'"& " where username = "&"'"&A1&"'"& ";" & " Update Transactions Set Username = "&"'"&B1&"'"& " where username = "&"'"&A1&"'"&";" & " Update UserNotes set Username = " &"'"&B1&"'"& " where username = "&"'"&A1&"';"& " Update History set Username = " &"'"&B1&"'"& " where username = "&"'"&A1&"';"

    The line shows:
    Update UsersALL Set Username = '3888' where username = 'smooth'; Update Transactions Set Username = '3888' where username = 'smooth'; Update UserNotes set Username = '3888' where username = 'smooth'; Update History set Username = '3888' where username = 'smooth';

    I want the line to show the Username = '003888'

    • ="Username ='"&RIGHT("000000"&G19,6)&"'"

  14. I have a similar problem with Excel dropping the leading zero in mobile phone numbers and landline phone numbers that people entered into a web form that exported the data to an spreadsheet (xls) file. Some cells, because of how they entered the numbers (with spaces, for example) automatically formatted as text, so the leading zero was preserved. Most of the numbers formatted as numbers and dropped the leading zero. Can I automate adding a leading zero to numbers conditionally? The condition is: "if the first digit (on the left) in the number is 3 or 4, put a zero in front" only if the cell is formatted as a number (not text). And convert the cell to text format. Can this be done?

  15. 4-Jan-20
    0-Jan-00
    6-Jan-20
    0-Jan-00
    0-Jan-00
    3-Feb-20
    i want to remove remove 0-jan-00 but data want to in date format

    • =IF(LEFT(G19,1)="0","",G19)

  16. How do I add preceding Zeros (in Mass) to all existing numbers already populated in cells within a column? I want to keep numbers where they are but add 000 Zeros to all cells at once. Please help! Can it be done?

  17. I need some value in a cell 01-01
    when i write the value like this 01-01 and then press the enter
    after changed the value like this 1-jan

  18. Thank you, thank you!!! I was racking my brain on this one, and not only spent an hour trying various things, but spent hours manually adding in many hundred of leading zeros that were deleted when I used the replace function to remove the leading word - now you honestly saved me many many more hours!

  19. I have some value in a cell 0000093203/01
    000000093203/12 but not in text format, how to remove starting zeros??
    Please help

  20. it want to combine year month and date columns to the following:
    2019-06-30

    I can get the month col as 06, but When I put it into the date format with the ampersand,
    I always lose the leading zero. Please help.
    Thanks,
    Larry

  21. This saved me an outrageous amount of time. Thank you so much!

  22. how to remove in phone number?
    here's the example phon number 0417 628 651 .i need to remove zero and this is the 417628651 ...

  23. hi
    i have downloaded a excell file of telephone numbers to edit and then upload to a telephone system.
    it has removed the leading 0 from all telephone numbers. i need to put the 0 back but keep the format as text. i have tried TEXT(value, format_text) but because its a 11 digit telephone number its saying there is a maximum of 20 digits.

    Can anyone help

  24. Hi
    I need a list of numbers to all be 10 digits with leading zeros making up the 10 digits.
    Original numbers can have different numbers of digits eg they could be 15153, 178013.
    These would need to be 0000015153, 0000178013
    I can see solutions here to always add one, or two digits, but not how to add a dynamic number of leading digits depending on the how many digits the number already has.
    Do you have any solution for this?
    Thanks

  25. Hello all,
    How to convert a number with only two decimal places?
    For example:
    If I type 10, it should come as 10.000
    If I type 100, it should come as 100.00
    How to do it? Thanks in advance.

    • hi sri,
      you can choose number format for that. it will help you.

  26. Dear Ladies and gentleman,
    I have a Number R00835 instead I need R0835.How to supress one zero.
    Thanking you in advance.
    Jorge Fernandes

    • =SUBSTITUTE(TRIM(SUBSTITUTE(YOURFIELD,"0"," "))," ","0")

  27. how to bank loan no create in excel sheet like 2121254000000000000000001111111111111

  28. I really need some help, i have time stamps (6:25) which is minutes and seconds but i need it to show the hour without having to manually ad the 0: in the front (0:06:25) How can i make 6:25 to show 0:06:25.

  29. I love the internet for this reason! thanks for helping out

  30. Hi,
    I want to add zero and space e,g (078 444 7777). But every time i add number it show like this (0784447777).

    • ="0"&(LEFT(A1,2)&" "& MID(A1,3,4)&" "&RIGHT(A1,4)) should work fine

  31. Instructions are clear and detailed and with plenty of illustrations. Great job, Svetlana!

  32. How should i enter the numbers in excel into ID format. For instance 17775608 should appear as 0000-0000-1777-5608

    • Hi Tasneem,

      Apply this custom number format: 0000-0000-0000-0000

  33. When entering 17 digits of RSBY, it shows last two digit as 00. Please guide for converting it to its original number. Eg: 32245614656464567, it shows 32245614656464500. Please guide me to correct this option.

  34. Hii
    i want to remove the extra zeros after the percentage to be 95% instead of 9500%

    • Ahmed:
      Can you initially enter the number as .095 then format it as a percentage? Right now, it's entered as 95 and then formatted as a percentage.

  35. Hi I have subtracts some number and generate a series and wants a suffix in this series.
    i.e.
    16 RR
    13 RR
    10 RR

  36. Best solution.

  37. Hi I have different numbers in row just now wants to add 0 before which starts with 4 how can we do that

  38. I want 5.25 to be 0-052 is this possible?

  39. i want to calculate how many zero in one cell can anyone help me in that for example i write 5000 in one cell in 5000 there is 3 zero in that figure.

  40. Thanks for the lesson.

    Please, is there a way one can retain the zeros in front of mobile numbers after the cell has been formatted and the mobile numbers copied and pasted instead of entering them one after the other?

    I'm trying to convert them to VCard Files but the numbers copied and pasted do not have the zeros in front of them after conversion unlike the ones that were entered individually.

    I will greatly appreciate your prompt response.

    Thank you.

  41. In my job, I have to type in nhs numbers on a spreadsheet. The old numbers were 6 digits, the new 8 digits. Logically, the first numbers (6 or 8 digits) began with a zero, until they passed the 099999 or 09999999. How can I display them in their true format, without formatting them as text? I have tried custom formats such as"00000000", but it still puts 2 zeros in front of a 6 digit number

    • Hi, Tim,

      you need to customize your number formats. Here we have a good tutorial on the process. The format code for you to use there:
      [>999999]00000000;[<=999999]000000

  42. Thank you very much!

  43. i want this number 095009419000000 to 95009419
    and this number 095094100090000000 to this 9509410009

    • That's what I want to do please help

      • Hello!
        To remove zeros from the beginning of text, use the formula

        =RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(A1&" ","0",""),1),A1&" ")+1)

        To remove zeros from the end of the text, use the formula

        =LEFT(D1,FIND("*",SUBSTITUTE(D1,RIGHT(SUBSTITUTE(D1,"0",""),1),"*", LEN(D1)-LEN(SUBSTITUTE(D1,RIGHT(SUBSTITUTE(D1,"0",""),1),""))),1))

        If you apply the second formula to the result of the first formula, you will remove the zeros both at the beginning and at the end of the text.
        Or you can use this formula

        =LEFT(RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(A1&" ","0",""),1),A1&" ")+1), FIND("*",SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(A1&" ","0",""),1),A1&" ")+1), RIGHT(SUBSTITUTE(RIGHT(A1,LEN(A1)- FIND(LEFT(SUBSTITUTE(A1&" ","0",""),1),A1&" ")+1),"0",""),1),"*", LEN(RIGHT(A1,LEN(A1)- FIND(LEFT(SUBSTITUTE(A1&" ","0",""),1),A1&" ")+1))- LEN(SUBSTITUTE(RIGHT(A1,LEN(A1)- FIND(LEFT(SUBSTITUTE(A1&" ","0",""),1),A1&" ")+1), RIGHT(SUBSTITUTE(RIGHT(A1,LEN(A1)- FIND(LEFT(SUBSTITUTE(A1&" ","0",""),1),A1&" ")+1),"0",""),1),""))),1))

        I hope this will help

  44. Flawless,

    thanks for your efforts

  45. wow

  46. Just put '

  47. kindly let us know how add in exl before 0
    like 01233

  48. :-) OK !

  49. WOW

    • Fantastic!

  50. Lots of thanks. Keep on.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)