Rod from Cincinati asks how to solve the problem of trailing spaces in the lookup table. I had never tried it before, but you can apply a TRIM to the entire lookup table. Episode 1138 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Bill: Hey welcome back, it's another MrExcel netcast, I'm Bill Jelen.
Well, you know, we do VLOOKUPs all the time, and one of the common things I talk about in my seminars, is when all the lookups go bad.
So here we have a situation, BG33-8, and I look over here, and it's in both places BG33-8, there's no reason why that shouldn't work.
And when I press F2 over here, you can see the flashing insertion point is right after the 8.
And then I come back over here and press F2, and we see the flashing insertion point a few spaces away from the 8.
And my standard example is to come in here and not look up A2, but to look up the trim of A2, alright.
So if you've been in my seminar, you've seen me do that, but we have a question today from Rod.
Rod is from Cincinnati and Rod- We actually have a video of Rod, here's Rod’s question!
Rod: Hi, this is rod from Cincinnati, I've got a question about VLOOKUPs.
Sometimes the values in my table array have trailing space behind them.
Therefore, even if I know that, for instance, I have Miami, I'm looking up Miami, and there is a Miami in the table right?
It doesn't match because of that trailing space.
I'm wondering if there's some way to deal with that within the lookup formula itself, instead of having to go into the table array and delete out those trailing spaces!
Bill: Alright cool, so Rod has the opposite problem!
In Rod’s situation, the lookup table has the extra spaces, and the data over here does not have the spaces.
And we could always, of course, insert an extra column, do the trim, Copy, Paste Special Values.
But I wondered if it would be possible, we could come in here, and actually do the trim of the entire lookup table.
Now this cannot be efficient, because for every record over here on the left-hand side, we are trimming well about 54 different values.
But hit Ctrl+Shift+Enter, and sure enough, it actually does it, so if you have this situation frequently, you know.
And most the time we're doing our VLOOKUPs just long enough to copy and paste the results.
You know, a quick way to go, and you don't have to worry about getting rid of those extra spaces over there on the right-hand side.
So hey, thanks to Rod for that great video question, thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Bill: Hey welcome back, it's another MrExcel netcast, I'm Bill Jelen.
Well, you know, we do VLOOKUPs all the time, and one of the common things I talk about in my seminars, is when all the lookups go bad.
So here we have a situation, BG33-8, and I look over here, and it's in both places BG33-8, there's no reason why that shouldn't work.
And when I press F2 over here, you can see the flashing insertion point is right after the 8.
And then I come back over here and press F2, and we see the flashing insertion point a few spaces away from the 8.
And my standard example is to come in here and not look up A2, but to look up the trim of A2, alright.
So if you've been in my seminar, you've seen me do that, but we have a question today from Rod.
Rod is from Cincinnati and Rod- We actually have a video of Rod, here's Rod’s question!
Rod: Hi, this is rod from Cincinnati, I've got a question about VLOOKUPs.
Sometimes the values in my table array have trailing space behind them.
Therefore, even if I know that, for instance, I have Miami, I'm looking up Miami, and there is a Miami in the table right?
It doesn't match because of that trailing space.
I'm wondering if there's some way to deal with that within the lookup formula itself, instead of having to go into the table array and delete out those trailing spaces!
Bill: Alright cool, so Rod has the opposite problem!
In Rod’s situation, the lookup table has the extra spaces, and the data over here does not have the spaces.
And we could always, of course, insert an extra column, do the trim, Copy, Paste Special Values.
But I wondered if it would be possible, we could come in here, and actually do the trim of the entire lookup table.
Now this cannot be efficient, because for every record over here on the left-hand side, we are trimming well about 54 different values.
But hit Ctrl+Shift+Enter, and sure enough, it actually does it, so if you have this situation frequently, you know.
And most the time we're doing our VLOOKUPs just long enough to copy and paste the results.
You know, a quick way to go, and you don't have to worry about getting rid of those extra spaces over there on the right-hand side.
So hey, thanks to Rod for that great video question, thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
