No Links were listed yet. Go ahead and share!
Bill Jelen: Hey, welcome back to the Mr. Excel netcast. I'm Bill Jelen amazing. We start out with massive amount of data so how are we going to analyze this. Well, let's fire up activity and see if we can solve this problem.
Hey, welcome back it’s another Mr. Excel netcast, I'm Bill Jelen. You know, we do view lookups all the time and one of the common things I talk about in my seminars is when all the lookups go back. So here we have situations BG33-8 and I look over here and it’s in both space both place BG33-8 there's no reason why that shouldn’t work. And when I press F2 over here you can see the flashing and search in point is right after the eight and then I comeback over here and press F2 and we see the flashing in searching point a few spaces away from the eight. And my standard example is to come here and not look up A2 but to look up the trim of A2.
All right, so if you been in my seminar you can see me do that. But we have a question today from Rod. Rod is from Cincinnati and Rod we actually have a video Rod. Here's Rod question.
Rod: Hi, this is Rod from Cincinnati I've got question about the lookups. Sometimes the values in like table array have the trailing space behind them therefore even I know that for instance I have Miami I'm looking up Miami and there is a Miami in the Miami at the table all right it doesn’t match because of the trailing space. I'm wondering if there some way to deal with that within the lookup formula itself and so that happen to go in to the table array and delete out those trailing spaces.
Bill Jelen: All right, cool. So Rod has the opposite problem. In Rod situation the lookup table has the extra spaces and the data over here does not have the spaces. And we can always of course introductory column do the trim copy paste special values but I wondered if it will be possible we get common here and actually do the trim of the entire look up table. Now this cannot be efficient because for every record over here on the left hand side we are trimming about 54 different values but hit control shift enter and sure enough it actually does it. So if you have this situation frequently and most of the time where doing our view lookups just long enough to copy and paste value the results.
You know a quick way to go and you don’t have to worry about getting rid one of those extra spaces over there on the right hand side. So hey, thanks Rod for that great video question thanks to you for stopping we’ll see you next time for another netcast from Mr. Excel.
Well, thanks for stopping we’ll see you next time for another netcast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services