Have you ever tried to enter an ID number that starts with zeroes? Excel automatically removes the leading zeroes, making it impossible for your VLOOKUPs to work. Rather than resorting to using a Zip Code format, try one of the tricks in Episode 392 to make Excel keep the leading zeroes.
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:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I really want to thank everyone who called in with questions for the netcast, if you'd like to call and just leave a message on my voicemail: 866-581-0221!
As I mentioned before, the podcast started out, really, going through the book “Learn Excel from MrExcel” and we've gotten to the point where I've talked about all 277 items in the book, almost.
And so I really need people to call in and give me new ideas.
Today we have a call: “Hi Bill, my name is Chris(?).
Uh, I figured this would be a great quick question(?).
One of my job posts is to compare sections of employee ID lists, using the VLOOKUP function in Excel to find matches.
Each ID is exactly 5 digits long.
The problem is when I entered the data, Excel doesn’t keep the leading zeroes which are in the ID.
So my problem, I must format the list as a 5-digit zip-code, then I can do VLOOKUP comparison.
How can I keep leading zeroes for 2, 3, 4-digit lengths?
Thanks!” Hey, that's a great question, the zip-code format is something I never even thought of.
If you select some cells and use Format, Cells, and then choose the Special category, you can choose zip-code, which will force everything to have 5 digits.
But of course that only works if your employee ID is five digits long.
What if you need leading zeroes in something that is 3, 4, or 5, or 6?
Well there's a couple of different ways to go.
The first way, which is a little bit annoying, but will actually work out better in the long run: Is instead of entering a number like 00123 and have Excel remove those leading zeroes, put in an apostrophe and then 00123.
The apostrophe tells Excel that we're not going to treat this as a number, it shouldn't be evaluated, and it will actually keep it as text.
So that's one way to go.
The apostrophe would be very frustrating, you had lots of employee IDs to enter, but the advantage there is that again, it would work with 1-2-3-4 leading zeroes, instead of the zip-code format.
The other way to go, and there's some gotcha’s when you use this, is to pre-select your range, and use Format, Cells, and then format the entire range as Text.
Once its format is Text, now if you enter the leading zero, 0123, it will keep the leading zeroes.
Text formatting, though, is a really evil kind of thing.
The problem with text formatting, is that if you have something formatted as text, and you enter a formula =2&* the number somewhere, instead of evaluating the formula, it leaves the formula as text.
Very annoying kind of thing to have the Text format.
So, I probably would kind of recommend just using the apostrophe, ‘00123, Excel keeps the leading zeroes for you, and convert just that cell to Text.
If the apostrophe is really too frustrating, then go through and format the whole range of employee ID as Text, and then as you enter them, it will keep the leading zeroes.
And if you happen to have an employee ID that's 5, I love the idea of using the zip-code format to force the leading zeroes to be there.
Of course, the place where that really comes in handy is with US zip-codes.
On the East Coast we have zip-codes that start with 0, and if you don't format them as zip-codes, then we'll lose the leading zeroes for all of those customers by the East Coast.
Hey thanks, that was a great call!
If you have a call for the podcast, just call us: 866-581-0221, and we'll get to your question, see you tomorrow for another Excel 2007 Thursday, thanks for stopping by!
I really want to thank everyone who called in with questions for the netcast, if you'd like to call and just leave a message on my voicemail: 866-581-0221!
As I mentioned before, the podcast started out, really, going through the book “Learn Excel from MrExcel” and we've gotten to the point where I've talked about all 277 items in the book, almost.
And so I really need people to call in and give me new ideas.
Today we have a call: “Hi Bill, my name is Chris(?).
Uh, I figured this would be a great quick question(?).
One of my job posts is to compare sections of employee ID lists, using the VLOOKUP function in Excel to find matches.
Each ID is exactly 5 digits long.
The problem is when I entered the data, Excel doesn’t keep the leading zeroes which are in the ID.
So my problem, I must format the list as a 5-digit zip-code, then I can do VLOOKUP comparison.
How can I keep leading zeroes for 2, 3, 4-digit lengths?
Thanks!” Hey, that's a great question, the zip-code format is something I never even thought of.
If you select some cells and use Format, Cells, and then choose the Special category, you can choose zip-code, which will force everything to have 5 digits.
But of course that only works if your employee ID is five digits long.
What if you need leading zeroes in something that is 3, 4, or 5, or 6?
Well there's a couple of different ways to go.
The first way, which is a little bit annoying, but will actually work out better in the long run: Is instead of entering a number like 00123 and have Excel remove those leading zeroes, put in an apostrophe and then 00123.
The apostrophe tells Excel that we're not going to treat this as a number, it shouldn't be evaluated, and it will actually keep it as text.
So that's one way to go.
The apostrophe would be very frustrating, you had lots of employee IDs to enter, but the advantage there is that again, it would work with 1-2-3-4 leading zeroes, instead of the zip-code format.
The other way to go, and there's some gotcha’s when you use this, is to pre-select your range, and use Format, Cells, and then format the entire range as Text.
Once its format is Text, now if you enter the leading zero, 0123, it will keep the leading zeroes.
Text formatting, though, is a really evil kind of thing.
The problem with text formatting, is that if you have something formatted as text, and you enter a formula =2&* the number somewhere, instead of evaluating the formula, it leaves the formula as text.
Very annoying kind of thing to have the Text format.
So, I probably would kind of recommend just using the apostrophe, ‘00123, Excel keeps the leading zeroes for you, and convert just that cell to Text.
If the apostrophe is really too frustrating, then go through and format the whole range of employee ID as Text, and then as you enter them, it will keep the leading zeroes.
And if you happen to have an employee ID that's 5, I love the idea of using the zip-code format to force the leading zeroes to be there.
Of course, the place where that really comes in handy is with US zip-codes.
On the East Coast we have zip-codes that start with 0, and if you don't format them as zip-codes, then we'll lose the leading zeroes for all of those customers by the East Coast.
Hey thanks, that was a great call!
If you have a call for the podcast, just call us: 866-581-0221, and we'll get to your question, see you tomorrow for another Excel 2007 Thursday, thanks for stopping by!
