August 25th, 2011
Tips, Tools and Tutorials
|Common Names:||Asterisk, Splat, Star, Wildcard|
|Range:||Computing (Typography, Programming languages & Web Development), Linguistics, Mathematics & Statistics|
|Conservation Status:||Least Concern|
Next to the Ampersand (&), the Asterisk (*) is one of my favorite symbols to use in Excel. But the love doesn’t just stop there…the asterisk is great for other uses as well. Outside of being used in automated touch tone telephony systems and denoting special conditions for special offers, the asterisk is actually quite an interesting, as well as dynamic, character (double entendre intended). Here are some ways that you can employ an asterisk and really show off some of your stellar (pardon the pun) Excel skills.
A Star is Born
Named for its resemblance to a star, the asterisk (aka splat or star) was, according to Wikipedia, created by family tree creators in feudal times to indicate date of birth and was originally a seven-armed star shape, though it can vary by 5, 6, 7 or 8.
The asterisk can often carry on a somewhat negative reputation not only because it can often indicate the presence of nearly impossible to read small printed footnotes, but because it can also be used to indicate a special circumstance which may belittle certain statistics, like the urban legend surrounding Roger Maris’ famous Babe Ruth record shattering home run record or Barry Bonds’ controversial 756th home run ball (though the asterisk is currently used to indicate a “great defensive play” in baseball).
However, in Excel and Internet Marketing, the asterisk is your friend! Not only is the asterisk used for commenting out notes in some computer programming languages, but perhaps the most useful of all, it is used as a wildcard. Of course, it can also be used to draw more attention to certain pieces of information, such as form fields that are required or in some scripting languages that use wrapping asterisks to emphasize/bold text. It’s the flexibility that the asterisk can provide that can really save you time and effort. Here are some ways that you can start using an asterisk to save time:
Though Google employs stemming technology to find variations of words in search, in some cases, an asterisk can be used for searching variations of phrases of a “base” word (e.g. searching for “diet*” could produce results for “diets”, “dieting” or “dietary”). You can also use an asterisk to finish a query, like “the hawaiian state fish is the *”, which unless you happen to be lightning fast at typing out humuhumunukunukuapua’a, it can save you a lot of time trying to find the information you are looking for. It’s a bit like Google Instant before Google Instant. Of course, you can also use it on the fly for a trivia question or if you suddenly forget something that is on the tip of your tongue.
Rectangulus Trigger Image Courtesy of Ken Yang
Need more than one piece of information? That’s okay, you can use more than one asterisk. Let’s say that you can’t remember what month AND day that Guy Fawkes Day is, you can just query “Remember, remember the * of *”. Granted, you may end up finding some results for V for Vendetta along the way.
Find & Replace just got a lot easier by using a wildcard. Let’s say you just copied over some text in HTML code and you don’t want all of the unnecessary tags (e.g. <p>, <em>, <strong>, <a href=”…”> ). You can use Find & Replace to remove each tag by listing “<*>” in the Find what field, and leaving the Replace with field blank.
By including the asterisk as a wildcard for anything that can be found within tags, then replacing it with nothing, you get rid of the tags and you’re left with just the text. This is helpful since the Paste Special > Values doesn’t work when you’re copying over the HTML code directly from a page’s source code. You can also modify it to replace domain names if you have a list of URLs that are migrating to another domain.
The fun doesn’t stop there, either! Let’s say you wanted to count how many cells contain text. This might be helpful if you have a range where you might be keeping notes or in the following example, information regarding 404 page errors. To keep track of which pages were checked, it makes sense to want to leave some sort of response, like “OK”. However, if you denote a numerical value, like “404”, with the formula =COUNTIF(F4:F13,”*”), you can count how many pages contain text (as opposed to numbers).
Another helpful use for the asterisk in Excel is to count the number of cells that contain a string of specific text. Though you could try to count cells with specific text individually, you can use the asterisk to help you find cells that contain a string of text that applies to all of the criteria you set. As an example, let’s say you have a list of URLs with their page names, but they’re not broken into separate columns. You could try to separate them all out and set up a COUNT function that way, or you can use the asterisk like so:
As you can see, this formula will count every cell that starts with “http://”. This, of course, also includes non-www URLs. If you want to set up a COUNTIF function to count how many cells also contain “www” so you can spot canonicalization issues with your list, just change the formula to
There are no comments yet.