Wildcards in Excel

Recently at work we had a situation where we had to remove just under 1,200 vendor part numbers from the names of our products. E.g., we had "Name of product (Vendor Part #123), 10 ounces." The vendor part numbers had gotten into our spreadsheet with said (internal) names accidently. (It's a long story. And it's not our fault.)

So I ended up writing a longer email about replacing these vendor part numbers with nothing (E.g., so pretend product name becomes "Name of product, 10 ounces.") and thought I might as well share it, editted only to make it generic. VP stands for Vendor Part, VP# for Vendor Part Number. (A serif font [a font with "feet"] is used to make some of the quotations, literally, clearer.)

I thought that this would work but decided to check first.

If you search for "(VP*)" and replace it with "" (nothing) all VP#'s should disappear. However, you'll have two problems, the first is that there will be a lot of double spaces, one from each side of the parentheses. So you'll have to do another find and replace, this time for "  " (two spaces) replacing it with " " (one space).

Also, in some cases ", " precedes the VP#, e.g., "Tumbler, (VP123) 10oz" which would yield "Tumbler,  10oz" when you were done. So you might want to search for ", (VP*)" replacing it with a single space, then searching for "(VP*)" replacing it with "", and then finally replacing all the double spaces with a single space. If you want to make sure there aren't double spaces run the same search a couple of times. When I've done this in the past I've left triple or quadruple spaces somewhere in whatever data I was working with. Each find and replace would take out one space, from 4 to 3, from 3 to 2, and finally from 2 to 1.

Obviously, all the searches don't actually have the quotes around them when you stick them into Excel. "*" is a wildcard that matches all characters. So if we are looking at the word "Wildcard", "Wild*" would match it, "*dca*" would match it and so on. "?" is a single wildcard that only matches a single character. Looking at "Wildcard" again, "?ildcard" would match, "Wil?card" would, "W???card" would, but "W?dcard" would not (missing either the "i" or the "l"). You can combine them: "?ildca*" would match. "*car?" would match any string that had "car" in it.

In Excel, prepending a tilde to the wildcards searches for the actual wildcard. So if a question mark is what you are looking for you need to prepend a tilde to it.

"*" and "?" are fairly standard wildcards, the preceding tilde to cancel them as wildcards isn't as much. Probably across MS Office, but I don't know beyond that.

Ok, that was a longer email than I meant to write...