A much better way of showing leading zeroes. After episode 392, several listeners called in with a better better way to show leading zeroes for any number of characters. Episode 397 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
OK hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Last Wednesday we had a call in question from Christian, who asked about creating employee IDs with leading zeroes.
In the particular case, he had a 5-digit employee ID, and we talked about using the zip-code format, but pointed out that this wouldn't work for 4, 3, 6, or 7-digit IDs, and I went into this big long explanation.
Well, jeez, I was on a trip, I was going through Green Bay and Miami and New York, and the email kept coming in.
I appreciate everyone who wrote to mention that I completely screwed up, there's a much easier way to go, I'm not going to play all the calls.
But I heard from Chad, Derek, Jeffrey, someone from Austin, someone from Washington, Randy and Charlotte, Jerry from Georgia, Pat in Wisconsin, Rick in Germany, Somebody named Angea, Bruce, Chris, Kevin, and Richard from the UK, all showing me that there's a much better way to do this and, of course, it makes a lot of sense.
We talked about using the zip-code field, which will always fill with leading zeroes.
So if I take this cell and format it using Format Cells, Ctrl+1 is the quick way to get to Format Cells, and we choose Special and then Zip Code, it will always fill up to 5 digits with leading zeroes.
But what if you wanted to fill with 3 leading zeroes or 4 leading zeroes?
Well, it's true that most of the formats can be modified using the Custom category.
So if I go back into Format Cells and choose Custom, you'll see that they created a code here of 00000, that's a 5-digit code, I really needed it to be 4 zeroes.
I'll just edit that custom code, click OK, and Excel will now display the data with 4 leading zeroes.
This works for anything up to 15 digits, so you can put up to 15 zeroes in there, and you’ll be good to go.
There's lots of other places where you can modify the built-in format, let's say that we have some sort of a decimal, and we want to display that in fractions.
If you go into Format Cells, underneath Fraction, there are options that you can say up to 1 digit, up to 2 digits, let's try that, and so 17/26.
But here, in Akron, Ohio, it's still a place where we care about 32nds.
The tire industry was kind of born here in Akron, and it's one place where we still talk about 32nds a lot.
So, after choosing a fraction with two decimals, you can go in and edit it, and say that we always want to display this as 32nds in the Custom category, Click OK, and it will round it to the nearest 32nd of an inch.
So, thanks to everyone who called, I really appreciate, there's obviously a much better way to handle employee IDs with leading zeroes of 3-4-5-6, anywhere up to 15 digits, by using the Custom category of the Format Cells dialog.
Hey, thanks for stopping by, we'll see you tomorrow, we'll have another Excel 2007 trick for you!
Last Wednesday we had a call in question from Christian, who asked about creating employee IDs with leading zeroes.
In the particular case, he had a 5-digit employee ID, and we talked about using the zip-code format, but pointed out that this wouldn't work for 4, 3, 6, or 7-digit IDs, and I went into this big long explanation.
Well, jeez, I was on a trip, I was going through Green Bay and Miami and New York, and the email kept coming in.
I appreciate everyone who wrote to mention that I completely screwed up, there's a much easier way to go, I'm not going to play all the calls.
But I heard from Chad, Derek, Jeffrey, someone from Austin, someone from Washington, Randy and Charlotte, Jerry from Georgia, Pat in Wisconsin, Rick in Germany, Somebody named Angea, Bruce, Chris, Kevin, and Richard from the UK, all showing me that there's a much better way to do this and, of course, it makes a lot of sense.
We talked about using the zip-code field, which will always fill with leading zeroes.
So if I take this cell and format it using Format Cells, Ctrl+1 is the quick way to get to Format Cells, and we choose Special and then Zip Code, it will always fill up to 5 digits with leading zeroes.
But what if you wanted to fill with 3 leading zeroes or 4 leading zeroes?
Well, it's true that most of the formats can be modified using the Custom category.
So if I go back into Format Cells and choose Custom, you'll see that they created a code here of 00000, that's a 5-digit code, I really needed it to be 4 zeroes.
I'll just edit that custom code, click OK, and Excel will now display the data with 4 leading zeroes.
This works for anything up to 15 digits, so you can put up to 15 zeroes in there, and you’ll be good to go.
There's lots of other places where you can modify the built-in format, let's say that we have some sort of a decimal, and we want to display that in fractions.
If you go into Format Cells, underneath Fraction, there are options that you can say up to 1 digit, up to 2 digits, let's try that, and so 17/26.
But here, in Akron, Ohio, it's still a place where we care about 32nds.
The tire industry was kind of born here in Akron, and it's one place where we still talk about 32nds a lot.
So, after choosing a fraction with two decimals, you can go in and edit it, and say that we always want to display this as 32nds in the Custom category, Click OK, and it will round it to the nearest 32nd of an inch.
So, thanks to everyone who called, I really appreciate, there's obviously a much better way to handle employee IDs with leading zeroes of 3-4-5-6, anywhere up to 15 digits, by using the Custom category of the Format Cells dialog.
Hey, thanks for stopping by, we'll see you tomorrow, we'll have another Excel 2007 trick for you!
