Leading zeros in Excel
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.
Labels: Excel, leading zeros
posted by Projectwoman @ 10:43 AM
3 Comments:
This was an incredibly AWESOME tip!!
I work with upc codes, and several thousand of them have a leading zero. Every time I import those, it would cause a problem in the table.
My developers were clueless---could not resolve it other than change the column to text after the csv was created, and then manually add the zero to the front of those that had been cut off.
Thank you so much!
Great tip, THANK YOU! I had been coverting to text, & concatenating the right number of zeros in lists of SSNs. This is so much easier.
EXCELLENT Answer! The other methods we were experimenting with were vastly more complex and didn't work as well. This solution is smart enough to change the number of leading zeros needed to maintain the desired number of digits, even not adding them at all if a number is correct to start with. Hat's off to you!!
Post a Comment
Links to this post:
Create a Link
<< Home