The ISNUMBER function in Excel does only one job–checks if a cell has a numeric value and returns TRUE (if it’s numeric) or FALSE (if it’s non-numeric).
What possible use could there be for this function?
Several, actually. Which is why we’re writing this article.
But first, let’s start with the syntax.
Syntax For ISNUMBER In Excel
The ISNUMBER function’s syntax is: =ISNUMBER(value)
This function takes only one argument, value. This can be numeric value, cell reference, or even another nested function.
The function returns TRUE if the value is numeric. In all other cases (text, errors, blanks), the function returns FALSE.
There is, however, one issue with this function–how it behaves across different Excel versions.
Behavior Across Different Excel Versions
Regardless of the Excel version you are using, the ISNUMBER function follows the same syntax and logic. However, the regional settings of your Excel application can impact the behavior of the this function. E.g., if the default number format uses commas as decimal separators instead of periods or the date formatting is different.
Here are some examples of the values returned by the ISNUMBER function when checked against different number and date styles:
If you encounter any specific issues with the ISNUMBER function behaving differently across Excel versions, it may be related to the regional settings or formatting of your data. Adjusting these settings can help ensure consistent behavior of the function.
Next, let’s look at how ISNUMBER works in Excel.
How ISNUMBER Works in Excel
Since the ISNUMBER function returns TRUE/FALSE, you can use it to identify non-numeric values in a cell which should have numeric values.
Example #1: Distinguish Between Numeric And Non-Numeric Cells
Let’s say you’ve calculated some values based on data in another worksheet, which has resulted in some cells containing numeric values, some being blank, and some containing errors.
The ISNUMBER can check for numeric values by using the formula: =ISNUMBER(A3)
When you drag down the formula until cell B10, you’ll see that the function returns TRUE only against cells containing values that Excel recognizes as being numeric (including dates).
Example #2: Employing ISNUMBER With IF For Custom Output
You can also nest the ISNUMBER function within IF to get custom output, instead of the default TRUE/FALSE.
Borrowing the data from the example above, we’ll use IF-ISNUMBER to output “Please check your calculation” if the value is non-numeric. Else, we’ll output “Your calculation is good.”
We’ll use the formula: =IF(ISNUMBER(A3),”Your calculation is good”,”Please check your calculation”)
Example #3: Utilizing ISNUMBER In Conditional Formatting
You can also use the ISNUMBER function when setting up conditional formatting rules in Excel.
To do this:
- Select the range of cells where you want to apply conditional formatting and set up a new formatting rule.
- Select the option “Use a formula to determine which cells to format.”
- Enter the ISNUMBER function in the “Edit the Rule Description:” field.
- Select the formatting by clicking on the “Format” button. In this case, we’ve chosen to have all cells containing numbers filled with yellow color.
After pressing the OK button, the cells will look like this:
These are just some basic use cases of the ISNUMBER function. You can, of course, combine it with other functions to extend their functionality.
Practical Example 1: Using ISNumber on a situational basis
In this example, suppose you have a dataset containing product IDs and their corresponding prices. You want to identify any cells where the price is not a numeric value.
The ISNUMBER function returns TRUE if the value in the referenced cell is a numeric value and FALSE otherwise. By applying this function to each cell in column B, you create a corresponding column in column D where TRUE indicates a numeric price and FALSE indicates a non-numeric price.
You can also apply Conditional formatting to the cells in column B based on the results in column C. Cells with FALSE (non-numeric prices) are formatted according to your chosen style, making them visually distinct.
Combining With Other Excel Functions
Here are a few examples of combining ISNUMBER with other functions:
Example #1. Checking For Specific Text Within A Cell Using ISNUMBER And SEARCH
Let’s say you have a long text string (like a sentence) in one column and want to check if a shorter string (like a word or phrase) is present in the long string.
To accomplish this, you can use the formula: =IF((ISNUMBER(SEARCH(B4,A4))),”Yes”,”No”)
Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.
Get Started
This formula searches for the string in B4 in the main string in A4. If present, the SEARCH function returns a number corresponding to the position of the word in the sentence.
When this is a number, if the string is present, the IF function modifies the TRUE output to “Yes.” But when this is !VALUE# error, if the string is not present, the IF function modifies the FALSE output to “No.”
Example #2: Case-Sensitive Search Using ISNUMBER And FIND
Since the SEARCH function is case-insensitive, so if you search for ‘A’ in the data set from the previous example, ISNUMBER will return Yes in all cases because each sentence contains either ‘A’ or ‘a.’
Instead, you can use the FIND function to do a case sensitive search. The formula for that will be: =IF((ISNUMBER(FIND(B4,A4)),”Yes”,”No”)
This will return the value “Yes” only in rows 4 and 6, which contain ‘A.’
Example #3: Using ISNUMBER With SUMPRODUCT
Let’s say you have sales data that looks like this:
You’ll notice that the multiplication function in cell C6 results in the #VALUE# error because cell A6 is non-numeric.
So, if you were to calculate the sum/average/min/max of all the data in the “Revenue” column, it’d result in the #VALUE! error too.
You can resolve this issue by using the SUMPRODUCT to calculate the values for the revenue column. The formula to achieve this is: =SUMPRODUCT(A4,B4)
When used in cell C6 (which previously had the #VALUE! error), this function will calculate revenue as 0.
Consequently, the average of the “Revenue” column will not show the #VALUE! error anymore.
These are some ways you can use the ISNUMBER function in Excel by combining it with other functions.
But when using ISNUMBER, there are a few things to keep in mind.
Important Considerations
The ISNUMBER function is part of a larger group of IS functions in Excel, each serving a specific purpose in logical evaluations. For instance, the ISTEXT function checks whether a value is text, the ISLOGICAL function determines if a value is a Boolean (TRUE or FALSE), and the ISDATE function identifies if a value is a valid date.
The ISNUMBER function is particularly useful in identifying and separating numerical values from other types of data, such as:
- Numbers stored as text: The ISNUMBER function can help you distinguish between genuine numeric values and text-based representations of numbers. E.g., an ISBN number. By using ISNUMBER, you can quickly identify cells containing numbers stored as text and then take appropriate actions, such as converting them to true numeric values or performing calculations on them.
- Dates and and times stored as numeric values: Excel stores dates and times as numeric values, which allows Excel to perform calculations and comparisons using functions like DATE, TIME, and DATEDIF. By applying the ISNUMBER function to a cell containing a date or time, you can confirm that Excel is indeed treating it as a numeric value, rather than as text or another data type. Thus, ensuring that your formulas are working with the correct underlying values and avoid potential errors or unexpected results.
- Excel’s formula error values: In Excel, error values (such as #DIV/0!, #N/A, or #VALUE!) are treated as non-numeric. Hence, the ISNUMBER function will return FALSE when encountering them. This is by design and helps maintain the integrity of your spreadsheet calculations. If the ISNUMBER function were to return TRUE for error values, it could lead to unexpected results or mask underlying issues in your formulas.
With that said, let’s wrap up our discussion on the ISNUMBER function.
Using The ISNUMBER Function In Excel
When dealing with a large data set with mixed data types, you can use ISNUMBER to maintain data integrity and avoid unnecessary errors when performing calculations. You can even combine ISNUMBER with other functions, like SEARCH, FIND, and SUMPRODUCT to perform operations that wouldn’t normally be possible with mixed data types.
Thus, ISNUMBER can make your life much easier when manipulating data within large worksheets.
But you know what else can make your life easier when working in Excel? Coefficient, the leading spreadsheet automation tool which can help you connect all your data sources, automate workflows, and share live insights within Excel. Get started today.
