Excel ISTEXT and ISNONTEXT functions with formula examples

ISTEXT and ISNONTEXT functions in Excel - usage notes

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:

• Both functions are part of the IS functions group that return the logical (Boolean) values of TRUE or FALSE.
• In a specific case when numbers are stored as text, ISTEXT returns TRUE and ISNONTEXT returns FALSE.
• Both functions are available in all versions of Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, and Excel 2000.

Check if a value is text

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)

ISTEXT for Data Validation: allow text only

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:

1. Select one or more cells that you want to validate.
2. On the Data tab, in the Data Tools group, click the Data Validation button.
3. On the Settings tab of the Data Validation dialog box, select Custom for the validation criteria and enter your ISTEXT formula in the corresponding box.
4. Click OK to save the rule.

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.

Excel IF ISTEXT formula

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.

Check if a range contains any 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")

Check if a cell contains specific text

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.