How to Use the Excel Functions ISNUMBER, ISTEXT and ISNONTEXT
IS functions are really fun to work with. They’ll return Boolean values (TRUE or FALSE) but can prove super useful when used the right way.
The ISNUMBER and ISTEXT function belong to the same IS family of Excel.
What are these functions used for, and how do you use them? Let’s learn all this and more in the guide below 😀
Also, if you want to practice these functions in real time, download our sample workbook here.
Table of Contents
How to use ISNUMBER in Excel
The Excel ISNUMBER function lies in the category of information functions. Its syntax is pretty simple and easy to use.
ISNUMBER checks a cell to see if the given value is a number. The value could be a simple number or the result of a formula.
It returns TRUE or FALSE and is often used in combination with other formulas. Let’s see the syntax below.
=ISNUMBER(value)
The ISNUMBER syntax only uses a single argument – value.
The value argument is the value we want to check. It can be a formula, cell reference, number, or anything else 🔟
To see how the ISNUMBER function works, read on.
We will apply the formula to the following data set.
Here’s the result the ISNUMBER function returns.
As evident, column A contains the values to be checked. Column B contains the results of the function and column C shows the formula used.
Column B returns TRUE for all numerical values and FALSE for all text strings.
Note that the ISNUMBER function returns FALSE for the number #854. That’s because it is not a number. The value is left aligned by default, indicating that it is not a number but stored as text instead.
Kasper Langmann, Microsoft Office SpecialistISNUMBER and IF formula example
Let’s see an example of the ISNUMBER and IF function 🤓
We have the following example data.
We want to find the salary of an employee in row 6 in the given data set.
But we want to keep the data private. To do that, we have used the IF function that will return the salary amount only if we use ID No.
If anyone tries to find the salary using names, the function will return an empty cell value.
The ISNUMBER function becomes important here as it checks if the given cell value is a number. If the selected cell is not a number, it will return a blank cell.
Let’s see this in action now, shall we? 👀
- Select a cell.
- Apply the formula as:
=IF(
- Add the ISNUMBER formula and cell value to check.
=IF(ISNUMBER(A6)
- Enter the value_if_true and value_if_false arguments as:
=IF(ISNUMBER(A6), C6, ” “)
- Press Enter.
And tada! Excel returns the expected result.
That’s because cell A6 contains a number, and we get the score corresponding to that ID No.
If we had used a cell reference containing a text string, for instance, cell B6, the function would return a blank cell. See the image below for reference.
As expected, we get an empty cell because cell B6 contains a text string.
Pretty simple, no? 😉
ISNUMBER SEARCH formula example
The SEARCH function looks up a cell for a specific substring. It returns the location of the value, but when compared with ISNUMBER, it returns the answer as TRUE or FALSE.
Let’s see an example of using the ISNUMBER and SEARCH functions together.
We have the following sample data.
Say we want to see if Roll No. 20 exists in cell B5. To do that:
- Select a cell.
- Enter the formula as:
=ISNUMBER(
- Add the SEARCH function.
=ISNUMBER(SEARCH(
- Enter the Roll No. and row you want to look for its location.
=ISNUMBER(SEARCH(20, B5)
- Press Enter.
Excel returns the result as TRUE.
Note that if we had used the SEARCH function alone, it would have returned a value “1”. But since we used it in combination with the ISNUMBER function, it returned TRUE.
That’s because the value is a number and exists in a specified location. If any of these conditions had not been fulfilled, Excel would have returned FALSE.
Kasper Langmann, Microsoft Office SpecialistHow to use ISTEXT in Excel
The ISTEXT function is similar to ISNUMBER. They have the same syntax arguments and perform the same kind of operation.
The only difference is that the ISTEXT function checks a cell for a text value, as evident from the name. It returns TRUE for text strings and FALSE for numeric values.
Its syntax is as follows:
=ISTEXT(value)
where value is the value to be checked.
We will use the previously used example data for the ISTEXT function.
Upon applying the ISTEXT function, we get the results:
The ISTEXT function returns TRUE for text values and FALSE for numbers.
Note that ISTEXT returns TRUE for the value #854. This proves that it is a text value as seen in the earlier ISNNUMBER example.
ISTEXT and IF formula example
Let’s see an example of the ISTEXT function with IF.
We will use the following example data.
We want to find the salary of Peter, but we don’t want to use his ID No. 😕
To do that, we will use the ISTEXT function, which will only show the salary when we enter his name. If anyone tries to check the salary using his ID No., it will return a blank cell.
Let’s see how to do it below.
- Select a cell.
- Enter the formula as:
=IF(
- Enter the ISTEXT function.
=IF(ISTEXT(
- Add the arguments for ISTEXT and IF functions.
=IF(ISTEXT(B7, C7, ” “)
- Press Enter.
Excel returns the result as:
If the cell reference used in ISTEXT function contained a number, the result would have been something like this:
As visible, the ISTEXT function returns a blank cell because cell A7 is not a text string.
How to use ISNONTEXT
The ISNONTEXT and ISNUMBER functions are identical. Both have the same purpose and the same syntax.
The ISNONTEXT function returns TRUE when a cell contains a numerical value. If it is a text string, it will return FALSE – same as in the case of ISNUMBER.
Syntax of the ISNONTEXT function is:
=ISNONTEXT(value)
Where value refers to the value to be checked.
It’s that simple to use.
A couple of tries are all you need to master this function 🧐
That’s it – Now what?
In this article, we learned how to use the ISNUMBER and ISTEXT functions. We also saw how to use the ISNONTEXT function and its uses in the practical world.
These functions are easy to learn and use. But identifying the utility of ISNUMBER and ISTEXT can be slightly difficult on the surface.
The power of these functions is unleashed when they are combined with other functions. You’ve already seen a quick glance through our examples. A little practice can help you master these functions in no time.
Some common functions you can combine the ISNUMBER and ISTEXT functions with include VLOOKUP, IF, SUMIF and others.
You can learn them in my 30-minute free email course that teaches these functions and more. It’s delivered right to your inbox only at the cost of your email address. So join now! 🤗
Other resources
Did you enjoy reading this article? If yes, then you’d love to learn more about such topics.
Read here: VLOOKUP function, IF function, and SEARCH function in Excel.