The tutorial looks at how to use the ISTEXT and ISNONTEXT functions in Excel to check if a cell contains a textual value or not.
Whenever you need to get information about the contents of some cell in Excel, you'd typically use the so-called Information functions. Both ISTEXT and ISNONTEXT belong to this category. The ISTEXT function checks if a value is text and ISNONTEXT tests if a value is not text. Whatever simple the concept is, the functions are amazingly useful for solving a variety of different tasks in Excel.
The ISTEXT function in Excel checks is a specified value is text or not. If the value is textual, the function returns TRUE. For all other data types (such as numbers, dates, blank cells, errors, etc.) it returns FALSE.
The syntax is as follows:
ISTEXT(value)
Where value is a value, cell reference, expression or another function whose result you want to test.
For example, to find out whether a value in A2 is text or not, use this simple formula:
=ISTEXT(A2)
The ISNONTEXT function returns TRUE for any non-text value including numbers, dates and times, blanks, and other formulas that return non-textual results or errors. For text values, it returns FALSE.
The syntax is the same as that of the ISTEXT function:
ISTEXT(value)
For instance, to check if a value in A2 is not text, use this formula:
=ISNONTEXT(A2)
As shown in the screenshot below, the ISTEXT and ISNONTEXT formulas return the opposite results:
ISTEXT and ISNONTEXT are very straightforward and easy-to-use functions, and you are unlikely to run into any difficulties with them. That said, there are a few keys points to take notice of:
Below you will find examples of practical uses of the ISTEXT and ISNONTEXT functions in Excel that will hopefully help you make your worksheets more efficient.
Sometimes when you are working with a bunch of values, you may be surprised to notice that for some numbers your formulas return wrong results or even errors. The most obvious reason is that problematic numbers are stored as text. The below formulas will tell you for sure which values are text from Excel's viewpoint.
ISTEXT formula:
Returns TRUE for any value that Excel considers text.
=ISTEXT(B2)
ISNONTEXT formula:
Returns TRUE for any value that Excel considers non-text.
=ISNONTEXT(B2)
In some situations, you may want to allow users to enter only text values in certain cells. To achieve this, create a data validation rule based on an ISTEXT formula. Here's how:
For this example, we are validating the questionnaire answers in cells B2 through B4 with the help of this formula:
=ISTEXT(B2:B4)
Additionally, you can configure your own Error Alert message to explain to your users what kind of data is accepted:
As the result, when the user tries to enter a number or date in any of the validated cells, they will see the following alert:
For more information, please see Using Data validation in Excel.
In practice, ISTEXT and ISNONTEXT are often used together with the IF function to output a more user friendly result than the standard TRUE and FALSE.
Taking our very first example a little further, supposing you want to return "Yes" for text values and "No" for anything else. To have it done, simply nest the ISTEXT function into the logical test of IF, and use "Yes" and "No" for the value_if_true and value_if_false arguments, respectively:
=IF(ISTEXT(A2), "Yes", "No")
In one of the previous examples, we discussed how to ensure valid user input by using Data Validation. This can also be done in a "milder" form with the help of an Excel IF ISTEXT formula.
In the questionnaire, suppose you want to determine which answers are valid (text) and which are not (non-text). For this, use the nested IF statements with the following logic:
Putting all this together, we get the following formula, where B2 is the cell to be checked:
=IF(B2="", "", IF(ISTEXT(B2), "Valid answer", "Invalid answer - please enter text."))
So far, we have tested each cell individually. But what if you need to know whether any cell in a range contains text?
To test the entire range, combine the ISTEXT function with SUMPRODUCT in this way:
As an example, let's check each row in the below data set for text values, which can be done with the following formulas:
=SUMPRODUCT(ISTEXT(A2:C2)*1)>0
=SUMPRODUCT(--ISTEXT(A2:C2))>0
One of the above formulas goes to cell D2, and then you drag it down through cell D5.
So, you now have a clear understanding which rows contain one or more text strings (TRUE) and which contain only numbers (FALSE).
If you'd like to return different results, say "Yes" or "No" as opposed to TRUE and FALSE, enclose the above formula in the IF statement:
=IF(SUMPRODUCT(--ISTEXT(A2:C2))>0, "Yes", "No")
The formula is based on the ability of SUMPRODUCT to handle arrays natively. Working from the inside out, here's what it does:
{TRUE,TRUE,FALSE}
SUMPRODUCT({1,1,0})>0
The Excel ISTEXT function can only determine whether a cell contains text, meaning absolutely any text. To find out whether a cell contains a specific text string, use either the ISNUMBER SEARCH formula or COUNTIF with wildcards.
For example, to see if the Item Id in A2 contains the text string input in cell D2, use the below formula (please mind the absolute reference $D$2 that prevents the cell address from changing when the formula is copied to other cells):
=ISNUMBER(SEARCH($D$2, A2))
For the sake for convenience, we'll wrap it into the IF function:
=IF(ISNUMBER(SEARCH($D$2, A2)), "Yes", "No")
And get the following results:
The same result can be achieved with COUNTIF:
=IF(COUNTIF(A2, "*"&$D$2&"*")>0, "Yes", "No")
For more examples, please see Excel If cell contains formulas.
The ISTEXT function can also be used with Excel conditional formatting to highlight cells containing text values. Here's how:
=ISTEXT(A2)
Where A2 is the leftmost cell of the selected range.
For more detailed explanation of each step, please see: Using formulas for Excel conditional formatting.
As the result, Excel highlights all the cells with any text strings:
That's how to use the ISTEXT and ISNONTEXT functions in Excel. I thank you for reading and hope to see you on our blog next week!
Excel ISTEXT and ISNONTEXT formula examples