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.
Related posts:
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