Authentic voices. Remarkable stories. AOL On Originals showcase the passions that make the world a more interesting place.
The story of punk rock singer Laura Jane Grace of Against Me! who came out as a woman in 2012, and other members of the trans community whose experiences are woefully underrepresented and misunderstood in the media.
Documentary shorts conceived of and directed by famous actors. Jeff Garlin, Katie Holmes, Alia Shawkat, Judy Greer, and James Purefoy
Park Bench is a new kind of "talking show" straight from the mind of born and bred New Yorker and host, Steve Buscemi.
Digital influencer Justine Ezarik (iJustine) is back. After covering the world of wearable tech last season, iJustine is expanding her coverage this year by profiling the hottest tech trends across the country.
Enter the graceful but competitive world of ballet through the eyes of executive producer, Sarah Jessica Parker. This behind-the-scenes docudrama reveals what it takes to perform on the ultimate stage, the New York City Ballet. Catch NYCB on stage at Lincoln Center.
Nicole Richie brings her unfiltered sense of humor and unique perspective to life in a new series based on her irreverent twitter feed. The show follows the outspoken celebrity as she shares her perspective on style, parenting, relationships and her journey to adulthood.
Explore what it means to be human as we rush head first into the future through the eyes, creativity, and mind of Tiffany Shlain, acclaimed filmmaker and speaker, founder of The Webby Awards, mother, constant pusher of boundaries and one of Newsweek’s “women shaping the 21st Century.”
Gwyneth Paltrow and Tracy Anderson spend time with women who've overcome hardship, injury, and setbacks to triumph in the face of adversity.
Hank Azaria’s touching, humorous, and often enlightening journey from a man who is not even sure he wants to have kids, to a father going through the joys, trials and tribulations of being a dad.
ACTING DISRUPTIVE takes viewers inside the businesses and passion projects of Hollywood’s top celebrities.
Follow Scott Schuman, the Sartorialist, from the streets of NYC to the capitals of Europe on his quest to photograph and document the best in culture and fashion.
Go behind-the-scenes with racing's hottest, young talent, 17-year-old Dylan Kwasniewski, as he aspires to make it in the #1 motorsport in America – NASCAR
Ahmed asks how to do a lookup that only looks at the text portion of the lookup value. Mike and Bill compare methods in this ...
Dueling Excel Episode 1169.
Tags:How to VLookup Text only in Excel,excel tutorial,Excel VLookup Function tutorial,text lookup value,VLookup Text Only,VLOOKUP text strings
Grab video code:
Transcript
How to VLookup Text only in Excel
Bill Jelen: Hey, I'm Bill Jelen from Mr.Excel.com and I’ve got a cool excel tip for you today.
Mike Gel Girvin: Hey! This Mike Gel Girvin in Excel is Fun in YouTube and I have a different way to do that.
Bill Jelen: Hey, welcome back. I'm Bill Jelen from Mr. Excel. Well, here it is the last podcast of 2009. Of course it’s a dueling excel podcast so Mike Girvin will be joining us.
Now Mike, I had someone sent in this question. I think it was Ahmed. Ahmed needs to do a look up, but he needs to do a look up on only the text portion. And Mike already gave me a heads up that the formula solution is horrible. So, I'm going to do what I always do and that’s VBA. Let’s switch over to VBA. AltF11, we’ll insert a module and I’ll say function, text only entry. So, entry is going to be the argument that gets passed. And what we’re going to do is we’re going to say 4i=1 to the length of entry. So, depending on how long the entry is, this CHAR is equal to the mid of entry, i, 1. So, this is just like using the mid function at Excel. And then what I need to do, select case, asc of this character. Now, the asc function in VBA is just like the code function in Excel. You see I started to head of in the wrong direction there. So that case is pretty cool.
Now, what I want to do is I want to find all the digits. So, the digits are case 48, that’s a zero, 49, 50, 51, 52, 53, 54, 55, 56, 57. Alright, if we get any of those, what am I going to do? I don’t want to do anything because I want to ignore the digits. What I'm really interested in is everything else, case else. And we’re going to say text only is equal to the old text only value which initially will be nothing, & this CHAR. Alright, that’s it. That’s the code. So this is going to run through each character. Let’s go and take a look and see if it is one of the digits zero through nine. If it is, it’s going to do nothing. Otherwise, it’s going to build the string text only. So, let’s try it here. We’ll try our function, make sure this is working. So, equal text only of that value. Look at that. It’s getting just the letters. Perfect, exactly what we need now. From here it’s a no brainer, right.
So VBA, not many lines—it didn’t take long to knock it out. Simple little loop. Check to see if it’s digits. It seems to be working relatively fast. And now Mike is going to have a much more elegant solution. I'm already going to give him the point. Let’s see what Mike has.
Mike: Thanks, Mr. Excel. Elegant! That VBA blows away. What I'm about to do right here, this is a messy formula. And here’s the trouble with this. When you’re trying to extract certain parts of text string like this, usually we have something like a pattern we can exploit, right? A dash. With a dash, you could do something like this—minus find and the left. If it was always the same three length for prefix, you just use left three. But in both these cases, there is no dash and it’s not just left three. Sometimes, there is two characters, sometimes they are one. Finally, you could use a look up a maximum number of characters in the cell, this search construction I’ve done video on this before. But that’s not going to work either because we have duplicate letters. There is an ‘s’ there and an ‘s’ there so we’re not, construction there is looking for s, it gets a true here and a true here. So, the only pattern we’re left with is there is letters and there is numbers. Now, it’s going to get messy. I'm going to start from the inside of the formula and build our way out.
The first thing is we need to extract all the individual letters and numbers and then start doing some analysis on that. So, I'm going to use row, indirect. And I'm going to construct in double quotes 1: N double quotes & the length because I need a string of numbers exactly the length of this to then extract the letters. So this way, it will give us a varying length. Sometimes, it will be 1, 2, 3, 4, 5. Sometimes, it will be 1, 2, 3, 4, 5, 6, 7—and if you highlight this and hit the F9 key, you can see we get that there are—further down, we’ll get 1, 2, 3, 4, 5 control Z. In that, what are we going to with that? We use the mid because our goal is to extract all the individual letters. So, we use mid of this comma. And we usually say starting position five or four, but this is an array and this is how we’ll get all of them. So, there’s all those starting positions comma. And how long do we want each one? One, so close parenthesis. If we F9 this, you can see we finally extracted the individual letters. Control Z. Now, we have letters.
What happens if I multiply this times one? I'm going to get where the numbers are and number about where the text is. I get an error. So if I hit F9—so now, we have something that differentiates the text and the numbers. Control Z. So from that, I'm going to say which one is error, which ones are error. And finally, now I have an array of true’s and false’s. That’s the first place where there’s a number, that’s the last place where there is a letter. Control Z.
So from that now, I need to find—let me just do that again because here’s one conceptual trick. That’s false. I need that position and match. When there’s duplicates like false, we’ll find the first one. So, we can use match and look up false. Control Z. So, use match and look up false comma. And I need to do an exact match because we want the first one and an exact match will find with duplicates, it always finds the first one if I hit F9. Finally, I get to a four, but I really want left how many? So, I need to control Z, minus one. Alright, and that’s how many for the left function.
So, I'm going come to the beginning, left of what, this is comma. Alright, so let’s see if this works. This is an array form that controls Shift Enter, double click and send it down. And sure enough you can see it gets that. Now, I'm going to just slap that in essence. This big monster here is the look up value and V look-up, so V look-up. That will be the look-up value, right? So, I come to the end and comma, the table array, that little thing right there. F4 comma, the second column is what we want to return, comma zero, for false close parenthesis. Control shift enter, that’s looking up. So, it’s actually looking up, but what do we want? This one divided by that. What a big nasty formula. Control shift enter, double click and send it down and there we have it. VBA wins the point and the elegance award.
Alright, see you next trick.
Bill Jelen: You have to love the way that Mike can explain that formula. Starting from the inside out, you can actually kind of follow what’s going along. Most people I think who encounter this formula would just be like, “What the heck is this?”
Now, I'm interested to see which is faster. Mike using array formulas here or the VBA. So in the past podcast, we’ve had this little trick where we can turn things back to menu calculation and actually time how long it takes the calculator range. So, let’s run that using Mike’s method. 0.023 seconds and let’s try the VBA. We’ll see how that works out. Choose the same range. Control S.04. So look at that. Mike’s method is calculated in just about half the time as the VBA method. Now, unless you have 100,000 rows, it’s an almost immaterial difference, two hundredths of a second, but much faster. We use that complicated array formula and we looked at a BBA. But hey, this was a great dueling excel podcast, two very difference ways of going out. I would never think to start to write that formula. I would just switch over to VBA. Mike was able to figure out the formula so that’s excellent.
I want to thank everyone for stopping by. We’ll see you next week for another dueling excel podcast from Mr. Excel and Excel is Fun.
Comments