Google Sheets provides a suite of functions called the “IS” functions.
These functions are not commonly used purely on their own but are heavily used by Google Sheets experts in combination with many other functions. You will find the most frequently used along with the IF function (or SUMIF/COUNTIF functions).
Among these, a very interesting function is the ISNUMBER Google Sheets function. This function lets Google Sheets check if it’s a number and returns either TRUE or FALSE based on that condition.
In this tutorial, I will take a look at what the ISNUMBER function does, its syntax, use, and how it can be cleverly applied to your Google spreadsheets. Read on to learn more.
Table of Contents
Google Sheets ISNUMBER Function – What Does it Do?
The ISNUMBER function in Google Sheets is fairly simple.
It checks if a value is a number and returns a corresponding Boolean value. There are two ISNUMBER values whereby if the value is a number, the function returns TRUE. if not, it returns FALSE.
Syntax of the ISNUMBER Function
The syntax of the ISNUMBER formula is:
ISNUMBER(value)
Here, value is the input to be tested. It can either be an actual value or a reference to a cell containing a value. You can also nest other functions inside the ISNUMBER function in Google Sheets.
For example, the following are all valid calls to the ISNUMBER function:
- ISNUMBER(25)
- ISNUMBER(B5)
- ISNUMBER($B$5)
- ISNUMBER(“30”)
- ISNUMBER(A124)
- ISNUMBER(FIND(A7))
Examples Using the ISNUMBER Function
To understand how the ISNUMBER formula works, I need to look at how it responds to different kinds of inputs.
In the image below, I test the Google Sheet ISNUMBER function with different types of inputs. Observe the outputs that ISNUMBER returns for each:
Notice that the date is also considered a numeric value since dates are internally represented as serial numbers in Google Sheets.
ISNUMBER can be used to determine the type of value in a data range before converting text to numbers.
You can use our copy of the ISNUMBER example sheet.
Common Applications of the ISNUMBER Function
As with other “IS” functions, the ISNUMBER function is seldom used on its own. When combined with other Google Sheets functions, however, it can have countless useful applications. Let us take a look at a few of them.
Combining ISNUMBER with IF to find Cells that Contain a Number
The ISNUMBER function combined with the IF function is probably its most common application of ISNUMBER.
When used on its own, the ISNUMBER formula simply returns a TRUE or a FALSE, which hardly makes much sense.
However, when you combine it with the IF function, you can return results that are much more meaningful. For example, if a value in a cell is not a number, you can display a more descriptive result, like “Not a number”, so that the person reading the spreadsheet understands exactly what you mean.
So, if you want to display in Google Sheets if a number is present in cell A1, your formula will need to be:
=IF(ISNUMBER(A1),"number" , "not a number")
The image below shows an example of an application like this:
Using ISNUMBER to Conditional Format Numeric Cells
You can also use the ISNUMBER function, in combination with Conditional formatting to highlight all the cells in a list that contain a number.
For example, say you have a list of values in column A, as shown below.
If you want to highlight only the cells that contain a number, you can do so in just a few steps:
- Select the cell range that you want to work on (A2:A8 in our example).
- From the Format menu, select Conditional Formatting.
- This will open the Conditional format rules sidebar on the right side of the window.
- In the Format rules section, click on the drop-down under “Format cells if…”
- Scroll down the drop-down list that appears and select “Custom formula is”.
- In the input box that appears under the dropdown, type the formula:
=ISNUMBER($A2)
- In the “Formatting style” section, click on the Fill Color button.
- Select the color you want to use, in order to highlight the numeric cells. I selected “light cornflower blue 1”.
- Click Done.
You should now see all cells that contain numeric values highlighted in light blue.
Note: To do the opposite, i.e. to highlight only the cells that do not contain numbers, change the formula in step 6 to: =NOT(ISNUMBER($A2)). The NOT function reverses the result returned by ISNUMBER here.
Combining ISNUMBER with SEARCH and FIND functions to Display a Descriptive Result
The SEARCH function searches a cell for a given text. If the text is found, the function returns its numerical position. If the text is not found then it returns a #ERROR.
The image below demonstrates how the SEARCH function works. Here, I am just looking if each cell contains the word “red” in it.
An error message as shown in the image above, often looks unaesthetic on your spreadsheet. But, if you can use the SEARCH function in combination with the ISNUMBER function, you can return a FALSE if the text is not found, and a TRUE if the text is found.
So, if you want to see if the string “red” appears in cell A1, an ideal way of using your SEARCH function would be:
=ISNUMBER(SEARCH(“red”, A1))
The image below shows an example of an application like this:
A function similar to the SEARCH function is the FIND function. It works more or less the same way as the SEARCH function. The only difference is that the FIND function is case-sensitive while SEARCH is not.
The image below shows how you can use the ISNUMBER function in combination with the FIND function to get results without the unattractive #ERROR messages:
To Check if a Range of Cells Contains a Number
The ISNUMBER function works with only individual cells. If you want to test a whole range of cells, then you can combine it with the SUMPRODUCT function.
The SUMPRODUCT function can sum up an array of values. So if I can use ISNUMBER to test each cell, convert the TRUE values to 1 and FALSE values to 0, I can combine their results with SUMPRODUCT and draw inferences about the entire range.
A very useful combination of ISNUMBER and SUMPRODUCT functions lets you find out if a range of cells contains at least one numeric value cell. Let’s take an example to understand how this can be achieved.
In the above image, I have three columns and I want to find out for each column if there exists at least one numeric value.
Here are the steps to solve this using ISNUMBER and SUMPRODUCT
- Select the cell where you want to display the first result (cell B7 in our example).
- Type in the formula:
=SUMPRODUCT(--ISNUMBER(B2:B6))>0
- Press the return key.
- This will display if the first column (column B) contains at least one numeric value.
- To copy the formula to the other columns, simply drag the fill handle of cell B7 to the right.
- Each column should now contain a TRUE or FALSE depending on whether the column contains a numeric value or not.
Explanation of the Formula
Let us breakdown the formula used in the above example to understand how it worked:
- First, the ISNUMBER function took each cell in the range B2:B6 and individually tested them. If the cell was a numeric cell, it returned TRUE, else it returned FALSE. So in the end, I got an array of TRUE/FALSE values. In other words, =ISNUMBER(B2:B6) gave the result {FALSE, FALSE, TRUE, TRUE, FALSE}
- Next, the ‘- -‘ operator lets you convert a FALSE value to 0 and a TRUE value to 1. Therefore, –{ FALSE, FALSE, TRUE, TRUE, FALSE} got converted to {0, 0, 1, 1, 0 }
- After this, the SUMPRODUCT function worked on the array: {0, 0, 1, 1, 0 } to return the sum of the values in the array. The result I got is 2.
- Finally, the ‘>’ operator was used to check if the value returned by SUMPRODUCT is more than 0. If there were no numeric values in the range, the sum of the array would be 0. Therefore, SUMPRODUCT would return a 0. In our example, since I got a 2 (which is more than 0), I got the final output is TRUE.
In this tutorial, I showed you what the ISNUMBER function does and how it can effectively be applied to your Google spreadsheets.
When Not to Use ISNUMBER
The Google Sheets ISNUMBER function is useful when checking if a value is a number. However, you should not use it when you want to check if a value is a text. This is because the formula will return FALSE for any value that is not a number and not only values that are texts. This includes an operation and a number in quotes.
Instead, you should use ISTEXT, which returns True if a value is a text string.
Frequently Asked Questions
What is the ISNUMBER Google Sheets Function?
ISNUMBER is a function that tells Google Sheets if a cell contains a number. It returns TRUE if the value is a number and FALSE for any other value, including numbers in quotations and operations.
Can you use ISNUMBER in an IF Statement?
You can use the if and ISNUMBER for more meaningful results. Google Sheets IF and ISNUMBER together can be used to return other values other than true and false, for example, “not a number”
Conclusion
There are a number of other ways in which you can apply the ISNUMBER function in Google Sheets, besides the applications shown here. The ISNUMBER Google Sheets function works best when used together with other function such as IF and SUMPRODUCT.
I encourage you to incorporate the function into your own applications and find newer ways to take advantage of its versatility.
I hope you found this tutorial useful:
