ISNUMBER Function
Checks if a cell contains a number or not
Checks if a cell contains a number or not
The Excel ISNUMBER function is categorized under Information functions. The function checks if a cell in Excel contains a number or not. It will return a TRUE if the value is a number and if not, a FALSE value. For example, if the given value is a text, date, or time, it will return a FALSE.
As a financial analyst, when dealing with large amounts of data, ISNUMBER Excel function helps in testing if a given result of a formula is a number or not.
=ISNUMBER(value)
The Excel ISNUMBER function uses the following arguments:
The ISNUMBER Excel function will return a logical value, which is TRUE or FALSE.
To understand the uses of the ISNUMBER Excel function, let’s consider a few examples:
Let’s first understand how the function behaves using the following set of data:
Data | Formula | Result | Remark |
---|---|---|---|
1 | =ISNUMBER(1) | TRUE | The value provided is a number, so the function returned TRUE. |
TEXT | =ISNUMBER(TEXT) | FALSE | The function returns FALSE for text values. |
10/20 | =ISNUMBER(10/20) | TRUE | The formula will return a number, so the function returned TRUE. |
#NAME? | =ISNUMBER(#NAME?) | FALSE | The function returned FALSE for formula errors. |
=ISNUMBER( ) | FALSE | The result is FALSE, as it is not a number. |
Suppose we wish to allow values that contain the text string XY. Using ISNUMBER along with data validation, we can check if Excel contains “XY” in the cell. Suppose we are given the following data:
To allow only values that contain a specific text string, we can use data validation with a customized formula based on the FIND and ISNUMBER Excel functions. Essentially, it checks if Excel contains a number in the cell or not. To do this, we will apply data validation to C4:C11 cells. To apply the data validation, click on Data tab, go to Data Tools and click on Data Validation, as shown below:
Now, select Data validation and click on Settings. As shown below, enter the formula =ISNUMBER(FIND(“XY”,C4)). To activate the formula cell, you need to choose Validation criteria – Allow – Custom.
When someone tries to change XY into something else, data validation rules are triggered, particularly when a user adds or changes a cell value.
The FIND function is shaped to search for the text “XY” in cell C4. If found, FIND will return a numeric position (i.e., 2, 4, 5, etc.) for the starting point of the text in the cell. If the text is not found, FIND will return an error. For C4, FIND will return 5, since “XY” starts at character 5.
The result from using the FIND function above is then evaluated by the ISNUMBER Excel function. For any numeric result returned by FIND, ISNUMBER will return TRUE and validation will succeed. When text is not found, FIND will return an error, ISNUMBER will return FALSE, and the input will fail validation.
Click here to download the sample Excel file
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!