Here is one way to count the number of labels that don’t contain a number in them:
You can download the file here to follow along. If you get a preview, look for the download arrow in the upper right hand corner.
I had a request from someone recently who wanted to know how to count the number of labels in a list that did not have numbers in them. There were two advantages I had with this scenario:
1) the numbers were always at the end of the list
2) I was able to use a helper column
Here is a section of that list:
I’m going to add a helper column right next to the list. The fist step in my logic is, since numbers are always at the end, let’s see what the last character in each label is. I will use the RIGHT function to extract that last character:
Notice how most are numbers, but there are some letters. Also, notice how everything is justified to the left. That means that Excel is interpreting these as labels not numbers.
My next step was to convert these to numbers. There are a few different ways to do this: adding a double negative in front, or multiplying or dividing by 1. I’ve chosen to add the double negative:
Now, everywhere there was a letter, Excel gives us an error, since it cannot convert those to values. So, my next thought was to wrap this in an IFERROR function, and just put a blank in the cell if it does result in an error:
Now, everywhere there was a letter at the end, the IFERROR function has inserted a blank. Now all I need to do is use the COUNTBLANK function to determine the number of blanks in the data:
Excel tells us that we have 7 labels in our list that do not have numbers at the end!
I’m sure there are other ways to accomplish this, maybe even to do it without a helper column. If you have a method in mind, add it to the comment field below or send me an e-mail at firstname.lastname@example.org. I’d love to see alternate ways to accomplish this.