Excel ISTEXT and ISNONTEXT functions with formula examples

This tutorial explores Excel's ISTEXT and ISNONTEXT functions, powerful tools for determining cell content. These information functions efficiently identify textual or non-textual values, proving invaluable for various Excel tasks.

• ISTEXT Function in Excel
• ISNONTEXT Function in Excel
• Formula Examples:
  • Identifying Textual Values
  • Text-Only Data Validation
  • Combining IF and ISTEXT
  • Checking Ranges for Text
  • Detecting Specific Text within Cells
• Conditional Formatting for Text Highlighting

Excel's ISTEXT Function

ISTEXT verifies if a cell's value is text. A textual value yields TRUE; otherwise, it returns FALSE (for numbers, dates, blanks, errors, etc.).

Syntax: ISTEXT(value)

Example: =ISTEXT(A2) checks cell A2's content.

Excel's ISNONTEXT Function

ISNONTEXT returns TRUE for non-text values (numbers, dates, blanks, errors). It returns FALSE for text.

Syntax: ISNONTEXT(value)

Example: =ISNONTEXT(A2) tests if A2 contains non-text. The results are opposite to ISTEXT.

Usage Notes:

• Both functions return Boolean (TRUE/FALSE) values.
• ISTEXT returns TRUE for text-formatted numbers.
• Compatible with Excel versions from 2000 onwards.

Practical Applications:

1. Identifying Textual Values: Quickly determine if numbers are stored as text, a common source of formula errors. Use =ISTEXT(B2) or =ISNONTEXT(B2).

2. Data Validation (Text Only): Restrict user input to text only.

1. Select cells.
2. Go to Data > Data Validation.
3. Choose Custom and enter =ISTEXT(B2:B4) (adjust range as needed).
4. Configure error alerts.

3. Combining IF and ISTEXT: Create more user-friendly output than simple TRUE/FALSE.

Example: =IF(ISTEXT(A2), "Yes", "No")

Another example providing feedback on questionnaire answers: =IF(B2="", "", IF(ISTEXT(B2), "Valid answer", "Invalid answer - please enter text."))

4. Checking Ranges for Text: Use SUMPRODUCT to efficiently check if any cell in a range contains text.

Example: =SUMPRODUCT(--ISTEXT(A2:C2))>0 (or =SUMPRODUCT(ISTEXT(A2:C2)*1)>0)

5. Detecting Specific Text: Use SEARCH or COUNTIF with wildcards.

Example using SEARCH: =IF(ISNUMBER(SEARCH($D$2, A2)), "Yes", "No")

Example using COUNTIF: =IF(COUNTIF(A2, "*"&$D$2&"*")>0, "Yes", "No")

6. Conditional Formatting: Highlight cells containing text.

1. Select cells.
2. Home > Conditional Formatting > New Rule > Use a formula.
3. Enter =ISTEXT(A2) (A2 being the top-left cell).
4. Choose formatting.

Mastering ISTEXT and ISNONTEXT significantly enhances Excel efficiency.

The above is the detailed content of Excel ISTEXT and ISNONTEXT functions with formula examples. For more information, please follow other related articles on the PHP Chinese website!