Return to List of Excel Functions

# ISNUMBER Function Examples – SEARCH, IF, NOT – Excel, VBA, & Google Sheets

Written by

Reviewed by

Download the example workbook

*This tutorial demonstrates how to use the ISNUMBER Function in Excel and Google Sheets to test if a cell is a number.*

## How to Use the ISNUMBER Function

The ISNUMBER Function tests if a cell is a numerical value. If yes, it returns TRUE, otherwise it returns FALSE.

`=ISNUMBER(A2)`

#### Things to Know

- In Excel,
**Dates**and**Times**are stored as numeric values. ISNUMBER will return TRUE when evaluating**Dates**and**Times.** - Numbers stored as text will return FALSE.

### If Cell is Number Then

Often, you’ll want to combine an “IS” function, like ISNUMBER, with the IF Function. With the IF Function, instead of returning a simple TRUE or FALSE, you can output specific text or perform specific actions if a cell is a number or not.

`=IF(ISNUMBER(A2),"number", "not a number")`

### Check if Not Number

With the NOT Function, you can test if a cell is not a number.

`=IF(NOT(ISNUMBER(A3)), "not a number","number")`

## ISNUMBER & SEARCH Formula

One of the most useful features of the ISNUMBER Function is the ability to evaluate if certain functions return a numerical value. For example, the SEARCH Function searches a cell for text. If the text is found, it’s numerical position is returned. If the text is not found an error is returned. By using the ISNUMBER Function we can return TRUE / FALSE if the text is found.

`=ISNUMBER(SEARCH(B$1, $A2))`

### ISNUMBER & FIND – Case Sensitive

The FIND Function works exactly the same as the SEARCH Function, except the FIND Function is case sensitive. In other words, the FIND Function will return FALSE when searching for “string” in “STRING” because the cases do not match.

```
=ISNUMBER(FIND("Red", $A2))
=ISNUMBER(SEARCH("Red", $A2))
```

### ISNUMBER & MATCH

The ISNUMBER Function can also be paired with the MATCH Function in a similar fashion. The MATCH Function searches for a value in a range of values. If the value is found, the function returns it’s numerical position. If it’s not found, it returns an error.

`=MATCH("Red", $A2)`

## Validate Cell Input

One use of the ISNUMBER Function is to validate the contents of a cell. For example, you might have an input form that requires a numerical value. With the ISNUMBER Function you can test if the user entered a numerical value, alerting them if they did not.

`=IF(B2="","",IF(ISNUMBER(B2),"","Invalid Entry"))`

### Data Validation – Force Text Entry

The above example was “weak” data validation; the user can ignore the warning message. Instead, we can use Data Validation to disallow non-numerical values.

Go to: **Data Ribbon** > **Data Validation** > **Data Validation**

and then select from one of these options:

## Test if Any Cell in Range is a Number

To test if any cell in a range of cells is a number, we can use the ISNUMBER Function along with the SUMPRODUCT Function.

`=SUMPRODUCT(--ISNUMBER(A2:A6))>0`

Follow the link above to learn how this function works.

### Highlight Cells That Contain Numbers

One last thing you can do with the ISNUMBER Function is highlight cells that contain text. We can do this by creating Conditional Formatting based on a rule.

- Select all the cells that you want to check
- Go to
**Home Ribbon**>**Conditional Formatting**>**New Rule** - Select ‘
**Use a formula to determine which cells to format**‘ - Enter the formula
**=ISNUMBER(A2)** - Click the Format button and choose your desired formatting
- Click OK twice to quit and save.

### Other Logical Functions

Excel / Google Sheets contain many other logical functions to perform other logical tests. Here is a list:

IF / IS Functions |
---|

iferror |

iserror |

isna |

iserr |

isblank |

isnumber |

istext |

isnontext |

isformula |

islogical |

isref |

iseven |

isodd |

## ISNUMBER in Google Sheets

The ISNUMBER Function works exactly the same in Google Sheets as in Excel:

## ISNUMBER Examples in VBA

You can also use the ISNUMBER function in VBA. Type:

`application.worksheetfunction.isnumber(value)`

For the function arguments (value, etc.), you can either enter them directly into the function, or define variables to use instead.