Comments on: How to add leading zeros in Excel

The 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. Continue reading →

Table of contents

Comments page 2. Total comments: 103

  1. Really a great post, appreciate your efforts

  2. Great post!

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

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

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

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

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

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

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

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

  9. thank you so much for your help.

  10. 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'

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

  11. 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?

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

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

  13. 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?

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

  15. 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!

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

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

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

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

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

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

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

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

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

    1. =SUBSTITUTE(TRIM(SUBSTITUTE(YOURFIELD,"0"," "))," ","0")

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

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

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

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

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

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

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

    1. Hi Tasneem,

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

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

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

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

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

  33. Best solution.

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

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

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

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

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

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

  39. Thank you very much!

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

    1. That's what I want to do please help

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

  41. Flawless,

    thanks for your efforts

  42. wow

  43. Just put '

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

  45. :-) OK !

  46. WOW

    1. Fantastic!

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