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