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...

Al, You're Done

Greenspan: Economy 'on the brink' of recession - CNBC TV- msnbc.com

Former Federal Reserve Chairman Alan Greenspan said Thursday that the U.S. housing market is “nowhere near the bottom” and that the U.S. economy is “right on the brink” of slipping into a recession...

Calling the current crisis in the financial markets a "once-in-a-century phenomenon," Greenspan said the Treasury had no choice in its recent moves to backstop the two government-sponsored mortgage finance giants, Fannie Mae and Freddie Mac. But he said the government will probably have to nationalize the two companies, calling them a "major accident waiting to happen."

I really cannot figure out what this guy is up to, he seems to have recently gone on a power trip and will not stop. It is extremely inappropriate for him to be constantly critiquing the U.S. fiscal policies at this point: Al, you already had 20 years as chair of the Fed, step down for real now.

I shouldn't say I can't figure it out, it is rather obvious. He is trying to set policy -- larger policies than he could have set while in office -- in "retirement." He is probably still trying to save his legacy, which has been tarnished by whatever, exactly, we are in (it is still difficult to call it a recession). The low interest rates enjoyed by Americans and the rest of the world in the early part of this millennium were largely set by Greenspan. They also are largely seen as creating the housing bubble which is still having major effects on the economy as banks fail, don't have extra capital, and people seeking capital (debt: mortgages, business loans, etc.) can't get it because of that.

Still, this is a bizarre show to be watching, even for Al.