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.
