ISNUMBER Function in Google Sheets (Check if Cell Contains Number)

Google sheets provide 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 commonly used along with the IF function (or SUMIF/COUNTIF functions).

Among these, a very interesting function is the ISNUMBER function.

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.

Google Sheets ISNUMBER Function – What Does it Do?

The ISNUMBER function is fairly simple.

It checks if a value is a number and returns a corresponding Boolean value. 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 function 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.

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 function works, I need to look at how it responds to different kinds of inputs.

In the image below, I test the ISNUMBER function with different types of inputs. Observe the outputs that ISNUMBER returns for each:

ISNUMBER Examples

Notice that date is also considered a numeric value since dates are internally represented as serial numbers in Google Sheets.

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 function simply returns a TRUE or a FALSE, which hardly makes much sense.

ISNUMBER to check a cell

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 whether the value in cell A1 is a number, 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 with IF in Google Sheets

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.

Conditionla formatting of numeric cells dataset

If you want to highlight only the cells that contain a number, you can do so in just a few steps:

  1. Select the cell range that you want to work on (A2:A8 in our example).
  2. From the Format menu, select Conditional Formatting.Click on Conditional formatting
  3. This will open the Conditional format rules sidebar on the right side of the window.Conditional formatting rules pane
  4. In the Format rules section, click on the drop-down under “Format cells if…Click on format cells if drop down
  5. Scroll down the drop-down list that appears and select “Custom formula is”.Select custom formula is
  6. In the input box that appears under the dropdown, type the formula: =ISNUMBER($A2)Enter the ISNUMBER formula
  7. In the “Formatting style” section, click on the Fill Color button.Click on the fill color button
  8. Select the color you want to use, in order to highlight the numeric cells. I selected “light cornflower blue 1”.Select the color to format the cells
  9. Click Done.Click on 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 are just looking if each cell contains the word “red” in it.

Search function to find a specific text

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:

using ISNUMBER with search

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:

Using ISNUMBER with find

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.

Dataset for ISNUMBER and SUMPRODUCT

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

  1. Select the cell where you want to display the first result (cell B7 in our example).
  2. Type in the formula: =SUMPRODUCT(–ISNUMBER(B2:B6))>0
  3. Press the return key.
  4. This will display if the first column (column B) contains at least one numeric value.Using SUMPRODUCT with ISNUMBER
  5. To copy the formula to the other columns, simply drag the fill handle of cell B7 to the right.
  6. Each column should now contain a TRUE or FALSE depending on whether the column contains a numeric value or not.SUMPRODUCT and ISNUMBER Result

Explanation of the Formula

Let us breakdown the formula used in the above example to understand how it worked:

  1. 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}
  2. 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 }
  3. 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.
  4. 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.

There are a number of other ways in which you can apply the ISNUMBER function, besides the applications shown here.

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:

Leave a Comment