How to Use excel Wildcards Video

Get it off your chest – be the first to comment on this video!

How to use Wildcards in a Filter - Excel Lesson

Bill Jelen: Hey! I’m Bill Jelen from Mr.Excel.com and I’ve got a cool Excel trip for you today. Mike Gel Girvin: Hey this is Mike Gel Girvin in Excel is Fun in YouTube and I have a different way to do that. Bill Jelen: Hey! Alright, it’s Friday that means it’s time for another Dueling Excel Podcast. I’m Bill Jelen from Learn Excel, join later by Mike Girvin from Excel is fun. Today, a question sent in by YouTube someone wants to figure out how to delete anytime of that swim or ski appears in the column. And they can be any version and variance of swim suits, swimming or skiing or swim wear. You want to delete all of those. I’m going to take a look at how to do this with filter and then with advanced filter. Now, in the filter drop down here of course we get to pick specific things. But I want to go to text filters and you see we have things like contains or does not contain. Now, both of these are going to lead to the same spot, the custom filters. And we’ll go to contains and now we get back to what we had in Excel 2003, the custom option under auto filter. And I can see that it contains ski or contains swim. Now, it’s interesting that in the old version Excel, we didn’t have contains. You have had said that you want to use equals and then put an asterisk before and after. Now, these are exactly equivalent items so we click OK and you see that we could adjust the swimming and skiing items. Now, we can select those, right click and delete row, delete the entire sheet row. That’s what I told you to do. Click OK. And when we clear the filter, we have just the non-skiing and swimming items. Alright, so that’s one way to go. Kind of a hassle to go through and set that up all the time. So, I want to show you something called an advanced filter. And now on the advanced filter, we’re going to have criteria range. The criteria range up here is going to say that “Hey, we’re looking for *swim*. In the next row that’s an *ski* and now we can come here and click advanced and say where the Filter the list, in-place, the criteria range is going to be this heading plus the two things we’re looking for, click OK and now we have just this swimming and skiing and I can delete the records just like before. Now, here’s an interesting one. Let’s make a copy of this and we’ll clear that filter. What if I wanted to show the things that aren’t swimming and skiing so we could copy those, well this is really wild. When you change this to be not equals to, if you’ve ever heard them called—when you have not, it needs to change the And’s to Or’s and the Or’s to And’s. If we would have used this criteria range right here, click Advanced, click OK, it doesn’t filter anything out. The trick is—because the reason it doesn’t filter anything out let me explain this is when it gets to swimming, it says oh well this should be shown because it’s not ski. And when it gets to skiing, it should be shown because it’s not swim. So, we need to take this data heading and copy it over the column F and then we’ll cut and paste. So now, we’ve joined these two things by Or. Hey, check this word. Is it not swimming or is it not skiing. And if either those are true, then it will be shown so we come back here to our advanced filter, it will change this criteria range. Change the criteria range with two columns by two rows, click OK. And now, we get the things that are not skiing and swimming. We can copy those and go to brand new slot and get just those items. Hey we’ll send it over to Mike. Mike Gel Girvin: Thanks Mr. Excel. Wow, I hope you guys had your notepad out, that was an amazing lesson in filtering. Hey, you got to see how in early version we use equals and then the wild card. In more recent versions, you could use contain, you got to see advanced filter. This was an Or criteria so we find swim or ski and then over here, totally brewing it. This is an And criteria because it’s on the same row. Each record to keep has to be not ski and not swim. Wow! Totally amazing! I’m going to do a formula here, come over here. Now, we’re actually going to use the Search function. Let’s see, how the Search function works. Search, we’re going to tell it to find some text like swim, F4 to lock it comma within this other text string. Now, search right now will deliver one because swim starts at the first character but down here go swimming get one, two, three, four so I’ll deliver a four. So, really this function is going to deliver an error or a number. So, we’ll use that pattern to our advantage later on. Now, but we didn’t get ski right? So, let’s come up here. I’m going to use the same trick Mr.Excel did. I’m going to highlight both of these and I’m going to highlight them and hit the F9 key. F9 key will convert and actually type out the array syntax. You see you don’t have to do it. Now, what’s nice is you don’t have to remember that curly brackets contain the array, that semicolons or rows and commas and column. ` Now, let’s see if this work, I’m going to control Enter, double click and send it down. Oh, but it didn’t pick up ski. Now, let’s try this. What if we double click and send this right here, this argument is one that’s giving us the trouble. It’s expecting a single text string we gave it to so it makes an array. If we do Ctrl+Shift+Enter, that’s not going to help either. Really what we need, notice the curly brackets because I did Ctrl+Shift+Enter, what we need is to put Search, the Search results inside of a function that can interpret the array part of this. The function to do that is Lookup. Now, Lookup is a function like some product they can handle arrays without Ctrl+Shift+Enter. But we need to give it a Lookup value. Remember, this Search function is only going to return the number of characters at the position where our text were looking for starts. Well, if we give it a Lookup value of one more than the maximum number of characters, this will work just fine. So, 2^15. Now 2^15, that’s one more character. That will just cover everything so it will always find a number, whatever number search beats out. I’m going to close parenthesis. Now, I got to tell you something. This formula right there, I saw this for the first time at the Mr. Excel message board at a challenge that Mr. Excel posted. It’s actually a contest and who—posted this formula. Totally awesome! Now, we don’t even have to Ctrl+Shift+Enter, all we have to do is Ctrl+Enter and double click and send it down. Now, there we have it. We have all the numbers. We have numbers and N/A not available. Now, I want to point out something about the 2^15 When I first saw this, I was like “What? How do I know that that’s the maximum number of characters?” I went over here and did 2^15. It’s sure enough it’s 32768. But then I did the repeat function. I said, “Hey, please repeat the explanation point 2^15 I want to do. If it turned an error it said, “Forget you. I know that that’s too mane characters but then I did 2^15-1 to repeat that and sure enough it worked. It should all the characters so that’s’ just the way that I’ve proved to myself that that number actually is the number of characters one more than is allowed. Now, what do we need to do here? Well, two things. If you want a true for the ski and swim then we’re going to take this range of N/As and one and put is number, that will give a true where the number is. If you wanted the true for the N/A, you’d use is. You’d use the Is and A right there but I’m going to use is number. Is N/A and Is number just looks at whatever it is and sys True or False. I’m going to Ctrl+Enter, double click and send it down. There we have it. We have our Trues for the ones we want to delete. If we’d use Is N/A, this could deliver a True. I’m going to click in one cell and sort. Right click sort, right click in sort that’s in 2007 not in earlier versions. Earlier versions just go to the data menu or data ribbon so I sort it. I got all the Trues at the bottom. I better more this one up here before I delete. And then now, I can just highlight the rows right just like that and right click Delete and so there is one way to do it with a formula, alright. We’ll see you in next trick. Bill Jelen: Mike, that is a beautiful formula. Excellent! I love those formulas—Excel message board Houdini and Alladin and other folks like that. Just to point out to people that I took some of those best formulas and put them on a book called Excel Gurus Gone Wild. So, if you like the trick that mike showed and want to learn more about wild formulas like that, formulas that frankly will make your head spin. Check out that book at your local bookstore. Hey, from Mike and myself, I want to thank you for stopping by. We’ll see you next time for another Dueling Excel Podcast.

No Links were listed yet. Go ahead and share!

Bill Jelen: Hey! I’m Bill Jelen from Mr.Excel.com and I’ve got a cool Excel trip for you today.

Mike Gel Girvin: Hey this is Mike Gel Girvin in Excel is Fun in YouTube and I have a different way to do that.

Bill Jelen: Hey! Alright, it’s Friday that means it’s time for another Dueling Excel Podcast. I’m Bill Jelen from Learn Excel, join later by Mike Girvin from Excel is fun. Today, a question sent in by YouTube someone wants to figure out how to delete anytime of that swim or ski appears in the column. And they can be any version and variance of swim suits, swimming or skiing or swim wear. You want to delete all of those. I’m going to take a look at how to do this with filter and then with advanced filter.

Now, in the filter drop down here of course we get to pick specific things. But I want to go to text filters and you see we have things like contains or does not contain. Now, both of these are going to lead to the same spot, the custom filters. And we’ll go to contains and now we get back to what we had in Excel 2003, the custom option under auto filter. And I can see that it contains ski or contains swim. Now, it’s interesting that in the old version Excel, we didn’t have contains. You have had said that you want to use equals and then put an asterisk before and after. Now, these are exactly equivalent items so we click OK and you see that we could adjust the swimming and skiing items. Now, we can select those, right click and delete row, delete the entire sheet row. That’s what I told you to do. Click OK. And when we clear the filter, we have just the non-skiing and swimming items. Alright, so that’s one way to go. Kind of a hassle to go through and set that up all the time.

So, I want to show you something called an advanced filter. And now on the advanced filter, we’re going to have criteria range. The criteria range up here is going to say that “Hey, we’re looking for *swim*. In the next row that’s an *ski* and now we can come here and click advanced and say where the Filter the list, in-place, the criteria range is going to be this heading plus the two things we’re looking for, click OK and now we have just this swimming and skiing and I can delete the records just like before.

Now, here’s an interesting one. Let’s make a copy of this and we’ll clear that filter. What if I wanted to show the things that aren’t swimming and skiing so we could copy those, well this is really wild. When you change this to be not equals to, if you’ve ever heard them called—when you have not, it needs to change the And’s to Or’s and the Or’s to And’s. If we would have used this criteria range right here, click Advanced, click OK, it doesn’t filter anything out. The trick is—because the reason it doesn’t filter anything out let me explain this is when it gets to swimming, it says oh well this should be shown because it’s not ski. And when it gets to skiing, it should be shown because it’s not swim. So, we need to take this data heading and copy it over the column F and then we’ll cut and paste. So now, we’ve joined these two things by Or. Hey, check this word. Is it not swimming or is it not skiing. And if either those are true, then it will be shown so we come back here to our advanced filter, it will change this criteria range.

Change the criteria range with two columns by two rows, click OK. And now, we get the things that are not skiing and swimming. We can copy those and go to brand new slot and get just those items. Hey we’ll send it over to Mike.

Mike Gel Girvin: Thanks Mr. Excel. Wow, I hope you guys had your notepad out, that was an amazing lesson in filtering. Hey, you got to see how in early version we use equals and then the wild card. In more recent versions, you could use contain, you got to see advanced filter. This was an Or criteria so we find swim or ski and then over here, totally brewing it. This is an And criteria because it’s on the same row. Each record to keep has to be not ski and not swim. Wow! Totally amazing!

I’m going to do a formula here, come over here. Now, we’re actually going to use the Search function. Let’s see, how the Search function works. Search, we’re going to tell it to find some text like swim, F4 to lock it comma within this other text string. Now, search right now will deliver one because swim starts at the first character but down here go swimming get one, two, three, four so I’ll deliver a four. So, really this function is going to deliver an error or a number. So, we’ll use that pattern to our advantage later on.

Now, but we didn’t get ski right? So, let’s come up here. I’m going to use the same trick Mr.Excel did. I’m going to highlight both of these and I’m going to highlight them and hit the F9 key. F9 key will convert and actually type out the array syntax. You see you don’t have to do it. Now, what’s nice is you don’t have to remember that curly brackets contain the array, that semicolons or rows and commas and column.

` Now, let’s see if this work, I’m going to control Enter, double click and send it down. Oh, but it didn’t pick up ski. Now, let’s try this. What if we double click and send this right here, this argument is one that’s giving us the trouble. It’s expecting a single text string we gave it to so it makes an array. If we do Ctrl+Shift+Enter, that’s not going to help either. Really what we need, notice the curly brackets because I did Ctrl+Shift+Enter, what we need is to put Search, the Search results inside of a function that can interpret the array part of this. The function to do that is Lookup. Now, Lookup is a function like some product they can handle arrays without Ctrl+Shift+Enter. But we need to give it a Lookup value. Remember, this Search function is only going to return the number of characters at the position where our text were looking for starts. Well, if we give it a Lookup value of one more than the maximum number of characters, this will work just fine. So, 2^15. Now 2^15, that’s one more character. That will just cover everything so it will always find a number, whatever number search beats out. I’m going to close parenthesis.

Now, I got to tell you something. This formula right there, I saw this for the first time at the Mr. Excel message board at a challenge that Mr. Excel posted. It’s actually a contest and who—posted this formula. Totally awesome! Now, we don’t even have to Ctrl+Shift+Enter, all we have to do is Ctrl+Enter and double click and send it down. Now, there we have it. We have all the numbers. We have numbers and N/A not available.

Now, I want to point out something about the 2^15 When I first saw this, I was like “What? How do I know that that’s the maximum number of characters?” I went over here and did 2^15. It’s sure enough it’s 32768. But then I did the repeat function. I said, “Hey, please repeat the explanation point 2^15 I want to do. If it turned an error it said, “Forget you. I know that that’s too mane characters but then I did 2^15-1 to repeat that and sure enough it worked. It should all the characters so that’s’ just the way that I’ve proved to myself that that number actually is the number of characters one more than is allowed.

Now, what do we need to do here? Well, two things. If you want a true for the ski and swim then we’re going to take this range of N/As and one and put is number, that will give a true where the number is. If you wanted the true for the N/A, you’d use is. You’d use the Is and A right there but I’m going to use is number. Is N/A and Is number just looks at whatever it is and sys True or False. I’m going to Ctrl+Enter, double click and send it down. There we have it. We have our Trues for the ones we want to delete. If we’d use Is N/A, this could deliver a True. I’m going to click in one cell and sort. Right click sort, right click in sort that’s in 2007 not in earlier versions. Earlier versions just go to the data menu or data ribbon so I sort it. I got all the Trues at the bottom. I better more this one up here before I delete. And then now, I can just highlight the rows right just like that and right click Delete and so there is one way to do it with a formula, alright. We’ll see you in next trick.

Bill Jelen: Mike, that is a beautiful formula. Excellent! I love those formulas—Excel message board Houdini and Alladin and other folks like that. Just to point out to people that I took some of those best formulas and put them on a book called Excel Gurus Gone Wild. So, if you like the trick that mike showed and want to learn more about wild formulas like that, formulas that frankly will make your head spin. Check out that book at your local bookstore. Hey, from Mike and myself, I want to thank you for stopping by. We’ll see you next time for another Dueling Excel Podcast.

Transcription by: Scribe4you Transcription Services

How to use Wildcards in a Filter - Excel Lesson

How to Use excel Wildcards -

How to Use excel Wildcards -