ISNONTEXT Function
An Excel Information function that evaluates and returns the result as TRUE numbers, boolean values, formulas, etc., and FALSE only for the text string.
What is the ISNONTEXT Function?
The ISNONTEXT function is an Excel Information function that evaluates and returns the result as TRUE numbers, boolean values, formulas, etc., and FALSE only for the text string.
Sometimes, I wonder how Excel was built ahead of its time. I mean, it’s 2023, and with all the advancements in AI and machine learning, Excel is still the software that has proved to be the most beneficial to the software industry.
AI has become a complementary technology to Excel and Microsoft products to serve users via the Copilot 365 tool better.
We have to appreciate all the hard work that went into the thought process of the initial developers at Microsoft, who imagined every possible way users would interact with Excel.
For example, take the text strings. They imagined that someone would want to evaluate whether the given values were text strings or not, and thus, the ISTEXT function was born.
On the other hand, they asked themselves,” What if the users wanted to evaluate all the non-text values?”
This thought gave rise to the ISNONTEXT function. In this article, we will learn about it, how to use it, and give an example.
- The ISNONTEXT function in Excel checks if a value is not text (i.e., numeric, logical, an error value, or blank).
- ISNONTEXT returns TRUE for numeric values, logical values (TRUE or FALSE), error values (like #N/A or #DIV/0!), and empty cells. ISNONTEXT returns FALSE for text values.
- ISNONTEXT is helpful in data validation and cleaning tasks where you need to check if a value is not text. It can be combined with other functions to create complex logical tests.
- If the value is an error value (like #VALUE! or #DIV/0!), ISNONTEXT returns TRUE.
ISNONTEXT function Formula
The ISNONTEXT is categorized as an Information function that evaluates to TRUE for all the values except the text strings.
Yes, you heard it right.
So if you have the value ‘Microsoft Inc’ or ‘Berkshire Hathaway Inc,’ the function will return the result as FALSE. On the other hand, if you have the value as TRUE, FALSE, 14th Dec 2021, 6898, etc., then the function evaluates to TRUE.
The syntax for the Isnontext Function is
=ISNONTEXT(value)
- value - (required) any hardcoded or referenced value such as text strings, numbers, boolean, or formula, which will be evaluated to check if it is non-textual in nature.
Note
Similar to all the Information functions, the ISNONTEXT returns the result as TRUE or FALSE. If we intend to return a customized text string for each result, we can use the IF function and the ISNONTEXT function.
How to use the ISNONTEXT Function in Excel?
Time and time again, we have mentioned that if there’s any function that is easy to use, then those are the information type.
And ISNONTEXT is no exception to this rule. Suppose you have the data as illustrated below:
By using the formula =ISNONTEXT(B3) in cell C3 and dragging it down till cell C8, we get
As we had expected, when the value was a text string, as in the case of cell B3, the result was FALSE. In all the other cases, when the value was not a text string, the function evaluates to TRUE.
We know that numbers, date, and time values are ultimately stored as numbers in excel, whereas errors are just ‘errors.’ Even though they look similar to the text strings, the ‘#’ character differentiates them into the error values.
Combining the ISNONTEXT function with the IF function can return two customized text strings for the TRUE and FALSE results!
For example, suppose our formula becomes =IF(ISNONTEXT(B3), "Not a text string", "Text string"), which gives the result:
Thus, you can easily combine the IF function to return whatever customized text string you need.
ISNONTEXT Function Alternatives
This is always one of my favorite sections of the article. I love reading about the functions upon which the article is based. But I absolutely love comparing and understanding the other functions that come close to the functionality or fall under the same umbrella.
In this case, several functions provide information in the form of boolean values similar to the ISNONTEXT function.
Some are ISTEXT, ISLOGICAL, ISFORMULA, ISERROR, ISNUMBER, ISBLANK, etc.
ISTEXT
The function that is closest to ISNONTEXT but works exactly the opposite is the ISTEXT function. The function will evaluate TRUE if it encounters a text string, while all the other values will be evaluated as FALSE.
Suppose we have the data as illustrated below:
We will use the formula =ISTEXT(B3) in cell C3 and drag it down to cell C8, which gives the result:
Only the text string ‘Microsoft Inc’ was evaluated as TRUE, while all the other referenced values returned as FALSE.
ISLOGICAL
ISLOGICAL will return the result as TRUE only for the boolean values, i.e., TRUE and FALSE. The function returns the result as FALSE if any other value is referenced in the formula.
Even though boolean values are also text strings, the ISTEXT function still would have no effect and return the result as FALSE.
Suppose we have the data as illustrated below:
We will use the formula =ISLOGICAL(B3) in cell C3 and drag it down, which gives the result:
Wasn’t that easy to understand? Only the booleans would be TRUE, while the rest of them would be FALSE.
ISFORMULA
The ISFORMULA function will evaluate whether the referenced cell contains a formula. If it is a formula, the function will return the result as TRUE, or else it will be FALSE.
Suppose we take a look at another example where we use the formula =ISFORMULA(B3) to give the result:
Anyone would wonder why is, are the errors, boolean values, or even numbers are evaluated as TRUE?
Well, that’s because all three of them have a single similarity between them - they are formulas and begin with an equal sign.
The boolean value in cell B3 is added as =TRUE while the number in B4 is added as =1.
In reality, the error value in cell B7 is a formula, i.e., = 1/0, which is why all three evaluate TRUE.
ISERROR
The ISERROR is the most important of the information functions you will use in Excel.
To catch all the errors, simply use the formula =ISERROR(B3), which gives the result as
As you can see, the function returns FALSE for all the values, such as text strings, numbers, dates, etc., and only evaluates to TRUE for the error values.
There are several error values such as #VALUE!, #NUM!, #NAME?, #REF!, #DIV/0! etc., evaluates to TRUE if the function encounters any of those errors.
ISNUMBER
ISNUMBER, as the name suggests, checks whether the referenced value is a number or not. If it is a number, the function returns the result as TRUE, or it will be FALSE.
Suppose we have the following data:
We will use the formula =ISNUMBER(B3) in cell C3 and drag it down to cell C8, which gives the result:
All the values stored as numbers, including the date and time values, are evaluated as TRUE, while the rest are evaluated as FALSE.
ISBLANK
The ISBLANK function can evaluate whether the referenced cell is empty. If the cell is empty, the function returns the result as TRUE, or if it contains the value, the value will be FALSE.
Suppose we have the following data:
We can already identify a couple of blank cells in column B, which are in cells B5 and B7. After using the formula =ISBLANK(B3) in cell C3 and dragging it down till cell C8, we get
Whenever the function encounters blank cells, it will evaluate TRUE.
Free Resources
To continue learning and advancing your career, check out these additional helpful WSO resources:
or Want to Sign up with your social account?