Using wildcards (asterisk, tilde, and question mark)

- In Excel, we sometimes need to use what are called wildcard characters: a question mark or an asterisk. Sometimes you'll need to use them with the Find & Select feature, that's a feature found on the HOME tab, the far right button. Or possibly when using a filter, or possibly when using any of the different COUNTIF function variations. There are six different functions ending in the letters IF, COUNTIFs, SUMIFs, etc. So let's explore some of these. First of all, in Column A, we've got some Part numbers.

In a real life situation we probably would have other columns adjacent to it that are related, but we need to isolate or view for the moment just those part numbers where H is the third character, as in the first two examples. So selecting Column A, and making sure the filter is active, if it's not, go to the DATA tab, choose Filter. I've got the filter in place here. Two ways to approach this: we could go to Text Filters, and choose Begins with. Now, I don't know what the first character is or do I care, nor the second character.

We only want to be sure that the third character is an H. So we could type ??, each of those stands for a character position. First character, we don't care, second character, we don't care, third character, capital H. Click OK. We see the entries. All the other rows are hidden, we see what's going on, all of those have H as the third letter. Another possible approach to this, as I first clear the filter, just to the right of the filter arrow, there's Clear.

Clicking the drop arrow, in the search panel, ??H We see nothing below this, typically we do, or we see the phrase "No matches," often we'll see entries here. So what follows the H? Well we don't care. And how many characters might there be? Well we don't know necessarily. The * symbol is a wildcard that refers to zero, one, or many characters. And now what do we see? That same list. So, different approaches to the same idea.

Now, let's take a different angle here. It's important to know in these code numbers where we do see ?? as the first two characters, then capital H, but then later in the text, the number 9, does it appear. So I'm going to put an asterisk then a 9, and we can see what's happening. But it looks as if it's only showing us the 9s when they're at the end. Let me take out the 9 momentarily, and you can see that it's showing others with a 9 in there, but the 9 isn't always at the end.

So, different approaches here. See how that looks, so we see those. Let's try this again by way of Text Filters. As we did earlier, we could start with all the entries that begin with first character, second character, we don't care, third character is H, then an *, and then a 9. What are we seeing this way? A shorter list, we see the two that end in 9 but also one that has a 9 here.

So that gets to the issue differently, so at different times you'll find that Text Filters, using Begins With in some cases, in other cases possibly even Ends With could be appropriate, or using the Search panel. One or the other might meet your needs exactly. Now, let's clear that filter and consider some other possibilities. Sometimes the characters that you're looking at are actual wildcard characters. I'd like to replace all the question marks here. So, in column E, let's go to the HOME tab, and use the feature called Find & Select, rightmost button, Replace.

So, I'd like to Find what: ?. Replace it with: nothing, just leave that blank. Replace All. What happens? You can see what's happening in the background, for the moment we've got to shut this down, Close, Ctrl + Z, get out of here, bring back the data. What if ? is the symbol we're trying to get at here? Let's put in "?" Replace that with nothing, Replace all.

And Excel says "Can't find it at all," so what's going on here? How do we get rid of question marks? If we were to change all the Ms here to Zs, let's do that, that should work. Let's find the letter M, so what do we do here with M? Do we simply put in M? Replace with nothing? What's going to happen here? 114 replacements. M are replaced all over the place with nothing, there had been an M in the top cell there. I'll close this, press Ctrl + Z, watch the entry in E2, the M will come back, as it did in some other cells too.

So we can get rid of Ms and Xs and 2s and 8s and 4s and everything else, how do we get rid of the question marks? Or replace them with something else, either one. Back to Find & Select, Replace. M, not M, this time, ? let's put a ˜ in front of it. An unlikely symbol to use, but this is what we need to use with the wildcard symbols. Let's replace question marks with, and let's say we put in Z.

So, looking ahead, rows 4 and down in row 12 there's at least two of them there, keep an eye on those, we want to replace the ? with Z. Let's replace all of them, 45 of them actually in the entire list. But, we see in row 4 there's a Z, also in row 12, and the question marks had been in those locations. So sometimes you'll have to use that capability. We might want to replace the asterisks with K, same general idea, Find and Replace, Replace.

This time, ˜* Replace all those with the letter K. Replace All, and we see what's happened there. 242 of them scattered throughout this list here. We can also use these in the COUNTIF function. I'm going to use this on a separate list, HR list over here. Let's imagine that we want to go through our list here, we want to know how many departments have the word service in them.

Now, we don't see too many here, we've got some down here farther. There's a couple here, there's a couple there, there might be some more. Professional Training Services, Project & Contract Services, how many different people are involved in service groups in this particular organization? So, I'm going to click right here, because I've frozen the top row so as we scroll down we'll be able to still see this. But I'm going to use the COUNTIF function. COUNTIF says we're looking at a certain location, let's say it's column C, comma, now no full entry in column C is the word service and nothing else.

So when we are looking for an entry, if we were, for example, looking for ADC, we could type "ADC" If we were looking for Admin Training, we would type "Admin Training" we could certainly do that But, we can also type "*Service we're looking for the word service. It's likely to be preceded, or could be preceded by something, we don't necessarily know that in all cases, *Service* It might be plural, we're just looking for the word service though.

By putting an asterisk on either side, remember, the asterisk means zero, one or many characters, we're about to say, let's look for wherever the word service occurs in column C. How many times does that occur? We end this with a " We don't have to type the right paranthesis, we'll complete the entry -- 114 times. Now, it so happens that in this list as I scroll down, most of these are near the end. So we can actually highlight these now here's where we see Service at the beginning of the entry, right here.

I'm going to highlight those, that's only five cells. But, as I move up here, got some others up here. Here's some more, I'm using the control key now to highlight these, quite a few there too. Project & Contract Services, and above that, Professional Training Services. So that's 99 rows, and possibly, and I already had five, so that's 104, and I think we have a few more, maybe up top.

I'm essentially verifying that 114 number, in here, we've got ten there, so we had 104 plus ten, 114. So, again, looking at this function COUNTIF, now, all the other related functions like SUMIF, AVERAGEIF, and then the three that are plural, COUNTIFS, SUMIFS, and AVERAGEIFS, all could potentially be using something like this. In other words, we're using the asterisk to refer to one or more characters, actually it can refer to no characters too. One of our entries was Service Bureau, it found that one as well too.

Or it counted that one, in this case. So, using an asterisk here. Now, it may be a little bit obscure, but we could do this too. We could say, for example, how many of the people's names there have the second letter being N. Admittedly, that would be very, very obscure, but, =COUNTIF, we're looking in column A, how many of those entries have the second letter as the letter N? So, " first character we don't care, that's ?, second letter is N, followed by any number of characters, that's an *" Enter Second character is N, for the entries in column A, how many of those are there? 11 of them. That would be a little bit tricker to find, we could probably do some sorting, some filtering, somehow to get there.

But, to verify that, I could actually move that up to row 1 so we can keep it. And by way of a filter to verify this, we could filter column A, data filter, Search: ?n* and there are the entries there. And there are 11 of them, there they are. So, admittedly unusual in this case here, but points out the idea, we're using the wildcard character ? here to refer to a single character, the * referring to zero, one, or many characters.

So, lots of variations on how we might use these, as we saw on this worksheet here. And also, on the previous worksheet here, using the Find & Select, Replace capability as well.