To check if a cell value is a number or not, you can use IF + ISNUMBER in a combination. In this combination ISNUMBER tests if a value is a number or not and returns the result as TRUE and FALSE. After that, IF uses that TRUE or FALSE to return a meaningful value in the result.
Formula to Test IF a Cell Value is a Number
To write this formula you can use the below steps.
- First, enter the IF function of a cell.
- After that, in the first argument of the IF (logical_text) enter the ISNUMBER function.
- Next, in the ISNUMBER function, refer to the cell where you want to test the number.
- Now, in the [value_if_true] argument, enter Yes, and in the [value_if_false] argument, enter No. You can also use some other meaningful values if you need.
- In the end, enter the closing parentheses and hit enter to get the result.
The moment you hit enter it returns YES or NO according to the value that you have in the referred cell.
How Does This Formula Works?
As I said, in this formula we have two functions, IF + ISNUMBER. Here, the “ISNUMBER(A1)” test checks whether the value in cell A1 is a number. It returns TRUE if A1 contains a number and FALSE if it does not (like text, an error, or a date).
Then, IF checks the condition results from the ISNUMBER. If the condition is TRUE, it returns the value_if_true (“Yes” in this case). If the condition is FALSE, it returns the value_if_false (“No”).
=IF(ISNUMBER(A1),"Yes","No")
Same Formula in Mac
If you use Excel for Mac, the formula will be the same and can be written using the same functions.
Excel stores a date as a number. And when you have a date in a cell, this ISNUMBER will treat that date as a number and return TRUE in the result. I’m trying to find a solution to this problem, still not able to differentiate between a number and a date while using this formula.
Other Formula to Check IF Cell has a Number Then…
The below formula will return “Not a Number” if the cell contains text or is empty and “Number” otherwise. But this won’t work perfectly for checking numbers as it assumes any non-text and non-empty value is a number, which might include dates or Boolean values (TRUE/FALSE).
=IF(OR(ISTEXT(A1), ISBLANK(A1)), "Not a Number", "Number")
And this formula checks if the value’s data type in cell A1 is a number (where 1 means a number). It returns “Number” for numeric values and “Not a Number” for anything else.
=IF(TYPE(A1)=1, "Number", "Not a Number")
Use SUMPRODUCT IF a Range has Numeric Values
Now, let’s say if you want to test an entire range instead of single cell, in this case, you can write a formula using SUMPRODUCT and ISNUMBER. Yes, you can use ISNUMBER in combination with SUMPRODUCT to count how many cells in a range contain numbers.
=SUMPRODUCT(--ISNUMBER(A1:A3))
To understand this formula, you need to split it into two parts: ISNUMBER checks each cell in the range to see if it’s a number, returning TRUE for numbers and FALSE for all other values.
The double hyphen (–) converts TRUE and FALSE into 1s and 0s, respectively. And then, SUMPRODUCT sums up these 1s and 0s, giving the total count of cells that contain numbers.