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 →

Comments page 3. Total comments: 103

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

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

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

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

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

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

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

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

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

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

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

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

  13. Best solution.

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

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

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

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

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

  19. Thank you very much!

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

  21. Flawless,

    thanks for your efforts

  22. wow

  23. Just put '

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

  25. :-) OK !

  26. WOW

    1. Fantastic!

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