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.
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
- 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.
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.
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?
📌 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.
📌 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.
📌 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.
📌 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.
📌 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.
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.
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.
🔎 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.
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.
🔎 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.
📌 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.
📌 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.
📌 Step 3:
➤ Select a random color that you want to highlight the rows with.
➤ Press OK.
📌 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.
Now you’ll be displayed the highlighted rows with the selected color as in the screenshot below.
💡 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.
