Everything Old Is New Again

In high school---I think it was my sophomore year---I ended up in a computer science class. Keep in mind that this was the mid-80's. The Earth was still cooling in terms of the personal computing age. I don't really know how I ended up in such a class, other than going to a very small school (had 59 people in my graduating class) meant electives were few and far between. This is not to say the experience was regrettable, just the things one notices when one is 15 are not always the most meaningful. For example, the teacher loved coffee and cigarettes and we were forever offering mints and gum in those desperate moments when we had to ask for help with our BASIC programming. I can still smell her breath every time I think about that class. And I have been thinking about it a lot this week. I finally broke down and bought my first guide to Visual Basic for Applications (VBA). My job is not centered around Excel, but I am reaching a point with a couple of projects where the formulas are getting ridiculous. When you need a dozen just to generate the right data for a simple bar graph, there has to be a better way. VBA is an evolution of the BASIC programming language. So, as I launch into this next phase of learning, I'm glad to see that it's my class from high school, all grown up (even if I am not) and sweet smelling.

Fast forward five years, when I am now a college graduate (srsly). The DOS star is rising from the Pacific Northwest. Windows has yet to be invented. If you wanted to use a computer program, you also had to know enough DOS to be dangerous. Do you remember how you could use an asterisk to search for files? (If you are too young to remember, I don't want to know.) The asterisk was the ultimate Scrabble blank. For example, "*.doc" would show you all the files with that extension, regardless of how many (of the 8 maximum possible characters) came before the extension. So much more handy than "?," which acted as a one-character wildcard.

Maybe you've forgotten about this, but Excel has not. And you know what? It makes for a pretty handy search item. I got to whip out this trick this week when a few of us were considering Excel as a tool to collect and organize some data.

How It Works
Use the "*" in a COUNTIF formula where you're not sure how many times something occurs. COUNTIF uses a range (for example, a column of data) and criteria (either numbers or text) and gives you a total. The formula looks like this: =COUNTIF(range,criteria) Let's say you've been using Excel to keep a record of students' behaviour in your class and you want to find out if the number of tardies has been reduced. You can use the COUNTIF function to find all of the instances.

What It Looks Like
Your records would be more extensive than this---a mix of observations over a given time period. But for example purposes, this will do. I have five records with the word "tardy" in a mix of places. You can see the formula in the formula bar at the upper right and the result in the highlighted A7 box.

Uh-oh. Excel didn't find any of the words "tardy." WTF? I can totally see the word "tardy." But Excel doesn't. It's looking for the word "tardy" all by its lonesome---not in a crowd of words.

This is where the * comes to save the day. We can place the symbol at the beginning of the text string to find all of the instances where the word "tardy" occurs last:

Or at the end of the string, to find all the instances where the word "tardy" is first:

Or we can go both ways, so to speak, to find all the cells which have the word "tardy" anywhere in them:

If you're wondering if the formula is case-sensitive, it's not. Look at cells A2 and A4 now, as well as the result of using the COUNTIF function.

What other uses for this little formula can you think of?

Bonus Round
You can also add conditional formatting to highlight the instances for you, for example all the times first period is mentioned: