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 on: How to add leading zeros in Excel
by Svetlana Cheusheva, updated on
Comments page 2. Total comments: 53
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
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
WOW
Fantastic!