Finding Asterisks

Learn Excel from MrExcel Podcast, Episode 2088:  Find Asterisks in a Filter

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.  Today's question sent in by Kim.  Kim has a dataset and she's using the Filter icon up here and she knows this cool trick where you can search for multiple things. 

For example, let's say that we want to search for a #.  Alright, so there's all things that have a # and then click OK.  And then you can open that up and essentially do and or- and search for anything that maybe has an @ sign.  Alright, and in this second step, you want to say Add current selection to filter and what we end up with is anything that has either # or an @ sign.  It's beautiful, but that's not what Kim is searching for.  Kim is searching for anything that has an * and this does not work because when we searched for *, I see we're getting all kinds of things that – Well, that one has an * but these don't.  What's up with that?  And I knew that this would also be a problem if Kim was looking for anything that has a ~ like this.  No matches.  Or a ? like that - no matches, alright.  And so the whole trick here, these are the three wildcards in Excel: * is find any number of characters; ? is find any character that wants specific, you know, length of 1; and then the ~ is the tool that says don't treat that wildcard as a wildcard.  Treat it as a character.  So, if we're looking for an * you have to search for a ~* like that, and we get just the things that truly have an *.  Cool. 

If you want anything that has a ? in addition, open it up and search for ~? and it’ll get you anything that has a ? and we'll Add current results to filter, OK.  And then what if you need to find a ~ ?  Well,  it’s search for ~~ like that, and we'll get anything that has a ~, Add current selection to filters, now we have anything that has either a ? or  a ~ or * like that. 

Now, this can get to some weird things.  If you needed to find, for example, ~? or ~~ , I have a ~~ in here.  You would have to search for  ~~  is looking for the first ~.  So anything that has a single ~ but then two more ~ says we want only the things that have two ~~ in a row.  Or maybe ~? and I don't have any of these.  I would find anything that had ~?.  Alright, kind of crazy now. 

How did I discover that last one?  Oh, because I was going to mention that Find and Replace, if you're going to search for an *, a ? or a ~, you have to do the same thing.  But here's the crazy thing in Find what.  You have to use the ~ but when you're replacing, you don't have to use the ~ .  Like, so, I tried this thing where I was going to get rid of anything,  let's say I was going to replace all of the ? with something else.  So Ctrl+H and I want to replace any ~? with ~*. Well, I don't need that ~ down there.  What I'm going to do is I’m going to change every ? to a ~*  which is going to be a problem - like right there.  Okay, so we’re going to undo that and try it again.  So, the top one you need the ~ , the bottom one, no, if I just want to change it to an * like in that bottom one that the wildcard doesn't work, so Replace All.  And that works, alright.  Crazy.

The other place where this comes in handy is VLOOKUP.  If you're looking up something that has a wildcard in it - So there I have Walmart, that's what I'm looking for.  I'm trying to get a 20 in response to that VLOOKUP but because that's a wildcard, any characters in there like Walmart will be the answer that's returned.  And that doesn't have to be a single character, it can be Wallingworldmart and it still gets returned as the answer.  So, in order to get truly Wal*Mart you have to look for Wal~*Mart.  Really, really frustrating. 

Same thing with a ?, I should be getting that answer but because there's a single character in QuestionAMark it's returned to 30.  You  have to search for ~? and then to look for - When I search for ~ there, that ~ would not followed by an *, it’s just pretty much ignored.  And so that answer is being returned.  If you search for 2 ~ it'll return the single ~.  Very, very confusing.

Now, these tips are in my book, Power Excel with MrExcel, 617 Excel mystery solved.  Click that “i” on the top- right hand corner to read more about the book.

Alright a quick recap: The Search box lets you filter for one thing and then filter for another thing but it fails if you’re trying to search for a *, ~ or ?.  Alright, it either returns everything in the case of the * or nothing for the ? or the ~.  To search for an *, you search for ~* or to find the ~, ~~ or to find a ?,  ~?.  That sets up some crazy things where you needed to find these three characters: ~ ? * .  You’d have to search for ~~ to look for the ~,  ~?,  ~* and it would find those 3 characters. 

Now, this also happens to the Find and Replace dialog box but only in the Find box do you need the extra ~.  Don't put it under Replace or it'll be throwing real ~ in there, instead of what you’re looking for.  Also VLOOKUP or MATCH.  You might need to use ~~~* or ~?.  If you’re specifically looking for a ~, an * or a ?.

Well, hey, thanks for stopping by.  We’ll see you next time for another netcast from MrExcel. 

So, just to show how that wildcards are supposed to work.  If you would search for A*E, you’re going to get anything starts with A and ends with E, with any number of characters in the middle.  But if you’re looking for just 3-letter words that have an A and then any character in the middle, followed by an E, putting that ? in there says we only want things that have a wildcard being one specific character.  Sometimes, there might be a use for that.