How to Use the ISNUMBER Function In Excel

Last Modified: June 3, 2024 - 8 min read

Hannah Recker
isnumber in excel

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:

isnumber examples

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)

isnumber with blank cells

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”)

isnumber function with conditional formatting

Example #3: Utilizing ISNUMBER In Conditional Formatting

You can also use the ISNUMBER function when setting up conditional formatting rules in Excel.

select range of cells to apply conditional formatting isnumber function

To do this:

  1. Select the range of cells where you want to apply conditional formatting and set up a new formatting rule.
  2. Select the option “Use a formula to determine which cells to format.”
  3. Enter the ISNUMBER function in the “Edit the Rule Description:” field.
  4. 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:

formatting use case with isnumber

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

excel tutorial example

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”)

Coefficient Excel Google Sheets Connectors
Try the Free Spreadsheet Extension Over 314,000 Pros Are Raving About

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
isnumber on text string in excel

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.’

search function with isnumber function

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”)

find function combined with isnumber

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:

multiplication function for isnumber formula

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.

sumproduct revenue

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)

sumproduct function

When used in cell C6 (which previously had the #VALUE! error), this function will calculate revenue as 0.

sumproduct example with revenue

Consequently, the average of the “Revenue” column will not show the #VALUE! error anymore.

average formula

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.

Sync Live Data into Excel

Connect Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 350,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Hannah Recker Growth Marketer
Hannah Recker was a data-driven growth marketer before partying in the data became a thing. In her 12 years experience, she's become fascinated with the way data enablement amongst teams can truly make or break a business. This fascination drove her to taking a deep dive into the data industry over the past 4 years in her work at StreamSets and Coefficient.
350,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 20,000 Companies