How to Use ISNUMBER Function in Excel (7 Examples)

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, the ISNUMBER function is generally used to check whether a given argument contains a numerical value or not. In this article, you’ll get to learn how you can use this ISNUMBER function efficiently in Excel with appropriate illustrations.

isnumber function overview in excel

The above screenshot is an overview of the article, representing a few applications of the ISNUMBER function in Excel. You’ll learn more about the methods along with the other functions to use the ISNUMBER function with ease in the following sections of this article.


Introduction to the ISNUMBER Function

isnumber function syntax

  • Function Objective:

The ISNUMBER function is used to check whether a value is a number or not.

  • Syntax:

=ISNUMBER(value)

  • Argument Explanation:
Argument Required/Optional Explanation
value Required Any value or cell reference or a range of cells.
  • Return Parameter:

A boolean value: TRUE or FALSE.


ISNUMBER Function in Excel: 7 Suitable Examples

1. Basic Use of Excel ISNUMBER Function

In the following picture, there are different types of data in Column B. In Column D, the outputs are showing if the selected data are numbers or not with boolean values: TRUE and FALSE respectively. As ISNUMBER function accepts a value as its argument, so in the first output Cell D5, the related formula will be:

=ISNUMBER("Andrew")

And the function will return the boolean value FALSE as ‘Andrew’ is a text, not a numeric value.

Similarly, you can apply all other values from Column B in the argument of the ISNUMBER function. The outputs are visible in Column D with the related formulas aside.

basic use of isnumber function in excel


2. ISNUMBER with Cell Reference in Excel

ISNUMBER function also accepts a cell reference or even a range of cells as its argument. So now let’s have a look at how the function works with the cell references of all the data present in Column B.

In the output Cell D5, the required formula with the ISNUMBER function with the cell reference (B5) of the name ‘Andrew’ will be:

=ISNUMBER(B5)

After pressing Enter, you’ll get a similar return value as found in the previous section.

You can extract all other outputs in Column D with the cell references of all the data from Column B the same way.

isnumber function with cell reference in excel


3. Use of ISNUMBER with Data Validation

Now we’ll use the ISNUMBER function for data validation. In the table below, Column C will contain only numerical values for ID numbers. If someone wants to input a text value or a letter, then an error message will appear. So how can we set these parameters for input criteria?

isnumber function with data validation in excel

📌 Step 1:

➤ From the Data ribbon, select the Data Validation command from the Data Tools drop-down.

A dialogue box named Data Validation will open up.

isnumber function with data validation in excel

📌 Step 2:

➤ Select Custom from the Allow list as Validation Criteria.

➤ In the formula box, you have to type:

=ISNUMBER(B5)

➤ Go to the Error Alert tab now.

isnumber function with data validation in excel

📌 Step 3:

➤ Type ‘Error!’ in the Title box.

➤ Input “Type a numeric value only” as the Error message.

➤ Press OK and you’re done with the setting of all required parameters for the input criteria.

isnumber function with data validation in excel

📌 Step 4:

➤ Now try to input a letter or an alphabet in Cell C5 and a message box will appear at once.

The message box will show the title and the error message as defined in the Data Validation dialogue box.

➤ Press Cancel and the message box will disappear.

isnumber function with data validation in excel

📌 Step 5:

➤ Now input a numerical value, for example, 115 in Cell C5.

And this time no message box will appear as the cell has been defined for a numerical input only.

isnumber function with data validation in excel

Read More: How to Use COUNTIF & ISNUMBER to Count Numbers in Excel


4. Combining ISNUMBER and SEARCH Functions to Find a Substring

Now we have a table in the following picture where Column B contains a number of text data. We have to find which cells in that column have the specific word- ‘Chicago’. We can use ISNUMBER along with the SEARCH function here to find the desired outputs.

For the first text value in Cell B5, the required formula to find the word ‘Chicago’ will be:

=ISNUMBER(SEARCH("Chicago",B5))

Press Enter and the formula will return the boolean value- TRUE.

Similarly, we can find the rest of the outputs in Column D by using Fill Handle to fill down the entire column.

isnumebr with search function to find substring in excel


5. Exploring if the Text Starts with a Number or Not with ISNUMBER, LEFT, and IF Functions

The LEFT function extracts the specified number of characters from text data. By combining ISNUMBER, LEFT and IF functions together, we can easily determine the texts containing a numerical value or a number at the beginning.

For example, based on the dataset below, the output cells in Column C will return ‘Yes’ for the criteria matched, otherwise will return ‘No’.

The required formula for the first text value will be:

=IF(ISNUMBER(--LEFT(B5,1)), "Yes","No")

Press Enter and autofill the entire Column C to get all other outputs at once.

isnumber and if functions to find number from the beginning in excel

🔎 How Does the Formula Work?

➤ Here the LEFT function extracts only the first character of the text.

➤ The use of Double-Unary (–) converts the text data into numerical ones.

ISNUMBER function then identifies the numbers only and returns the boolean values- TRUE, and FALSE for the non-numeric values.

➤ Finally, the IF function gathers the output of the logical function- ISNUMBER and returns ‘Yes’ or ‘No’ based on the boolean values- TRUE or FALSE respectively.

Read More: Use ISNUMBER Function with IF and Then Statements in Excel


6. Incorporating ISNUMBER and SUMPRODUCT to Find Columns That Contain Numbers

Now there are some random columns with a particular data type in each column in the following picture. With the combined use of ISNUMBER and SUMPRODUCT functions, we’ll find out the data types of all columns available.

For the first column, known as Column 1 in the header row 4, the required formula in Cell C11 to find the data type of this column should be:

=IF(SUMPRODUCT(--(ISNUMBER($B$5:$B$9)))>0,"Number","Text")

Press Enter and the formula will return ‘Number’.

By using a similar procedure, we can get all other data types for all other columns present.

isnumber and sumproduct to find columns containing numbers only

🔎 How Does the Formula Work?

➤ The ISNUMBER function returns boolean values TRUE or FALSE for all data in the selected column.

➤ The use of Double-Unary (–) converts each boolean value- TRUE to 1 and FALSE to 0.

SUMPRODUCT Function adds up the numerical values found in the previous step for the selected column.

➤ Finally, the IF function produces the logical argument with the SUMPRODUCT function to see if the output found from the preceding step is greater than zero (0) or not and returns ‘Number’ or ‘Text’ based on the findings.


7. ISNUMBER with Conditional Formatting in Excel

In the last example, you’ll get to learn how you can use the logical function- ISNUMBER in Conditional Formatting to highlight the cells or rows in a table based on the defined criteria. For example, in the following dataset, Column B is present with several donor names and IDs. With the Conditional Formatting, we’ll highlight the rows for those donors only whose ID numbers are visible in Column B and at the same time, among those who have donated greater than or equal to $1500.

isnumber with conditional formatting in excel

📌 Step 1:

➤ Select the range of cells B5:C14.

➤ Under the Home tab, select New Rule from the Conditional Formatting drop-down.

A dialogue box will appear.

isnumber with conditional formatting in excel

📌 Step 2:

➤ Select the rule type: ‘Use a formula to determine which cells to format’.

➤ In the formula box, type:

=AND(ISNUMBER($B5),$C5>=1500)

➤ Click on the Format option.

isnumber with conditional formatting in excel

📌 Step 3:

➤ Select a random color that you want to highlight the rows with.

➤ Press OK.

isnumber with conditional formatting in excel

📌 Step 4:

➤ A preview will be shown at the bottom bar of the New Formatting Rule dialogue box.

➤ Press OK and you’re done with the steps.

isnumber with conditional formatting in excel

Now you’ll be displayed the highlighted rows with the selected color as in the screenshot below.

isnumber with conditional formatting in excel

Read More: [Fixed] Excel ISNUMBER Not Working


💡 Things to Keep in Mind

🔺 Although the ISNUMBER function takes the argument as a value or a cell reference, you can also input a formula to explore if the resultant value is a numeric value or not.

🔺 In Excel, dates and times are also numeric values. So, the ISNUMBER function will return TRUE for dates and times in the strings.

🔺 ISNUMBER function is a member of IS group of functions.

🔺 The function does not return any error as it only examines a given input to be numerical or not.

🔺 You cannot directly input date or time in the argument of the ISNUMBER function. Otherwise, the function will return FALSE. You have to use the DATE and TIME functions to input a date or a time for the ISNUMBER argument.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


Concluding Words

I hope all of the suitable methods mentioned above to use the ISNUMBER function will now inspire you to apply them in your Excel spreadsheets with more productivity. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo