Find Cells Containing Numbers With Excel's ISNUMBER Function

Find Cells Containing Numbers with Excel's ISNUMBER Function
Find Cells Containing Numbers with Excel's ISNUMBER Function. © Ted French

Excel’s ISNUMBER function is one of a group of IS functions or “Information Functions” that can be used to find out information about a specific cell in a worksheet or workbook.

The ISNUMBER function's job is to determine if the data in a certain cell is a number or not.

  • If the data is a number or is a formula that returns a number as output, a value of TRUE is returned by the function - the example in row 1 in the image above;
  • If the data is not a number, or the cell is empty, a FALSE value is returned -  the example in rows 2 in the image above.

Additional examples show how this function is often used in conjunction with other Excel functions to test the outcome of calculations. This is usually done to gather information about a value in a particular cell before using it in other calculations.

The ISNUMBER Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the ISNUMBER function is:

= ISNUMBER ( Value )

Value: (required) - refers to the value or cell contents being tested. Note: By itself, ISNUMBER can check only one value/ cell at a time.

This argument can be blank, or it can contain data such as:

It can also contain a  cell reference or named range pointing to the location in the worksheet for any of the above types of data.

ISNUMBER and the IF Function

As mentioned, combining ISNUMBER with other functions - such as with the IF function - rows 7 and 8 above - provides a way of finding errors in formulas that do not produce the right type of data as output.

In the example, only if the data in cell A6 or A7 is a number is it used in a formula that multiplies the value by 10, otherwise the message "No Number" is displayed in cells C6 and C7.

ISNUMBER and SEARCH

Similarly, combining ISNUMBER with the SEARCH function in rows 5 and 6 creates a formula that searches the text strings in column A for a match to the data in column B - the number 456.

If a matching number is found in column A, as in row 5, the formula returns the value of TRUE, otherwise, it returns FALSE as a value as seen in row 6.

ISNUMBER and SUMPRODUCT

The third group of formulas in the image use the ISNUMBER and SUMPRODUCT functions in a formula that checks a range of cells to see if they contain numbers or not.

The combination of the two functions gets around the limitation of ISNUMBER on its own of only checking one cell at a time for number data.

ISNUMBER checks each cell in the range - such as A3 to A8 in the formula in row 10 - to see if it holds a number and returns TRUE or FALSE depending upon the result.

Note, however, that even if one value in the selected range is a number, the formula returns an answer of TRUE - as shown in row 9 where the range A3 to A9 contains:

  • empty cells;
  • text data;
  • an error message (#DIV/0!);
  • the copyright symbol (©);
  • and one number in cell A7 which is enough to return a value of TRUE in cell C9.

How to Enter the ISNUMBER Function

Options for entering the function and its arguments into a worksheet cell include:

  1. Typing the complete function such as: =ISNUMBER(A2) or =ISNUMBER(456) into a worksheet cell;
  2. Selecting the function and its arguments using the ISNUMBER function dialog box

Although it is possible to just type the complete function manually, many people find it easier to use the dialog box as it takes care of entering the function's syntax - such as brackets and comma separators between arguments.

ISNUMBER Function Dialog Box

The steps below outline the steps used to enter ISNUMBER into cell C2 in the image above.

  1. Click on cell C2 - the location where the formula results will be displayed.
  2. Click on the Formulas tab.
  3. Choose More Functions > Information from the ribbon menu to open the function drop down list.
  4. Click on ISNUMBER in the list to bring up that function's dialog box
  5. Click on cell A2 in the worksheet to enter the cell reference into the dialog box
  6. Click OK to close the dialog box and return to the worksheet
  7. The value TRUE appears in cell C2 since the data in cell A2 is the number 456
  8. If you click on cell C2, the complete function = ISNUMBER ( A2 ) appears in the formula bar above the worksheet