If you want to know how many numbers are in a given group of cells, there’s an easy way to do it: use the count() function.
Imagine, for a moment, that you have a list of 100 dates. The question is — are they all numbers? It matters because if they’re numbers, you can use a variety of functions to extract the year, month, and day. If they’re not numbers, then to extract the year, month, and day you have to use text-manipulation functions such as right(), left(), and middle().
So you can take your group of dates, and use count() on them. If the dates were in a1 through a100, the formula would be =count(a1:a100). If the result is one, they’re all numbers. If the result is zero, they’re all text. If it’s somewhere in between you’ve got a mixed of numbers and text, which you may be able to separate by sorting it. It may ask you if you want to sort text that looks like numbers as if it were numbers, and if you say no then the two formats will end up separated.
Count() will also let you check more than one range at a time. You could count all the numbers in column A and all the numbers in column B in the same function. It’s worth noting, however, that if two ranges overlap, then you’ll be counting any numbers in the overlapping section twice. For example, if you counted all the numbers in column A, and all the numbers in row 1, then if cell A1 had a number in it then your result would have one more number than is actually there.
All that said, let’s take a look at what does and does not count as a number. In the tables below I’m going to give you a variety of things, showing both the actual contents of the cell (the formula), and what you see if you’re not inside the cell, showing the formula (the result).
|=today()||5/27/12||This returns today’s date, as a number.|
|=pi()||3.14159||Remember geometry? area=π r2? pi() returns π|
|8717044007104620||8.717E+15||This is scientific notation — very helpful when you’ve got a whole lot of digits and only care about the most significant ones. It’s essentially saying 8.171 * 10^15.|
|‘3||3||Note the ‘ instead of the =. That makes it a character, even if it’s a character that normally represents a number.|
|‘Three||Three||The spelled-out names of numbers are not numbers.|
|1 2 3 4 5 6||Pretty much, if it has characters that are neither numbers, part of a function name, or mathematical operators, it’s not a number. This even counts spaces.|
|=left(“05/27/12”,2)||05||“05”, since it was extracted from text, using a text manipulation function, is still text.|