This is a fun solution. You want to put a series of numbers in a range in Excel and you want them to have leading zeros. So you want, 001, 002, 003 etc. Problem is that Excel drops the zeros when you type them. It makes sense, leading zeros aren’t required and really don’t aid comprehension. However, for your own reasons, you want them.
Here’s how to do this:
Select the cells and choose Format > Cells > Number tab. Select Custom from the Categories list and in the box marked Type:, type this:
00#
This tells Excel that there must always be 3 digits showing which forces leading zeros to be displayed. It doesn’t do anything to the numbers so they are still numbers which is just as you would want it to be.
I am so grateful for this post. How great is it that I can type such an odd request into Google and find this solution immediately! Thank you, this was perfect.
You are very good, but does anyone have a clue why this and other obvious formats are not a part of Excel? Seems I am always trying to figure out something that seems common, but needs a VB macro or you need to stand on your head and scream like a chicken to get it to work.
Hi Helen. I am often awestruck by your skills and knowledge across a wide range of subjects. Have you ever considered publishing a special issue of PC User with all your historical tips and spreadsheets in it?
However, though the drive roared and rattled, I could not find any tutorial on the March 2010 DVD about Ecel String Functions. Ken Pettit
i’ve just come across a need for this, and while i would expect excel too allow you to do it, your solution worked very well.
thank you for posting.
Madam, Somehow what you are saying works ONLY for display. If there is a need for the format to be retained when copied to another location, this does not work. Even =rept(“0”,5)+f3 will work only to the extent that it gets replicated when copied and pasted using special paste>values option. However, in the edit bar, it would still show the figure as one without the leading zeros. Is there some method to show the leading zeros in the edit bar?
I need this because the software i use requires to read the data from another database which has the leading zeros. Any help please!
You are right, this only works for display purposes. The only way to ‘keep’ the leading zeros is to prefix the cell entry with a single quote mark – ie a ‘ so it looks like:
‘0005639
Then it will show leading zeros but it is now a piece of text and not a number so you cannot perform any calculations using it.
cheers
Helen
I insert a column (B) and use B1=text(a1,”000″); Copy it over all the rows and use paste special to change the functions to values, then you can delete column A.
Dear all!
Thank you for your thoughts. Somehow, i forgot to tell the community that i found the solution that i was looking for:
=RIGHT(CONCATENATE(REPT(“0”,5),F13),5)
Where, column ‘F’ contains the numbers. This way, not only does it display, but also retains the leading zeros when pasted (paste special>values).
@Marietjie, I am sorry, i tried your method. I was unable to get this type of result. Thank you for attempting to solve the problem.
Thank you so much for this helpful advice – we were all going nuts in the office trying to make the leading zeros to stay put!
Ace post, this has been driving me craaaaaaazy
Thanks
re:
>prefix the cell entry with a single quote mark – ie a ‘
Another way to force the cell to be treated as text rather than number, is to simply Format it as Text.
This has the advantage that you can do so for an entire column (or table/range, or worksheet, or workbook) in one step.
ie, select the cell/range/worksheet/workbook you want to set up this way, press Control-1, and on the Number tab choose the Text option.
Thanks helen – that works perfectly