How to Use the ISTEXT Function in Google Sheets [Example Formulas]

Published on

The ISTEXT function is one of the “Info” category functions in Google Sheets that help prevent or identify mixed data types.

The purpose of the ISTEXT function is to test a value and return TRUE if it’s text; otherwise, it returns FALSE.

You can use it in data validation to ensure data consistency and in conditional formatting to identify mixed data types. Additionally, you can use it in logical tests in formulas.

As a side note, you can avoid mixed data types in Google Sheets by converting your existing range to a table and choosing data types for each column.

Syntax of the ISTEXT Function in Google Sheets:

ISTEXT(value)

Where value is the value to be verified.

Basic Formula Examples

=ISTEXT(100) // returns FALSE
=ISTEXT("InfoInspired") // returns TRUE
=ISTEXT(A1) // returns TRUE if A1 contains text, else FALSE

You can use ISTEXT in a range to verify each value in it by entering it as an array formula:

=ArrayFormula(ISTEXT(A2:A10))
ISTEXT array formula example

ISTEXT Function in Logical Tests

Example 1: To set an alert

You want to alert a user when they make an invalid entry. For example, if you expect an ID in cell B2, which should be text, the following formula in cell C2 will return “Please enter a valid ID” if the entered value is not text:

=IF(ISTEXT(B2), "", "Please enter a valid ID")

Example 2: To test if all the values in a range are text

The following formulas will return the number of text values in the range C2:C10. If the result is 0, it means all values are non-text, such as dates, numbers, or times:

=ArrayFormula(COUNTIF(ISTEXT(C2:C10), TRUE))
=SUMPRODUCT((ISTEXT(C2:C10)))

These formulas will help you identify and count text values in the specified range.

ISTEXT Function in Data Validation

To prevent any entry other than text in a cell, for example, cell B2, you can use the following ISTEXT formula in Data Validation in Google Sheets:

=ISTEXT(B2)

Steps:

  1. Click Data > Data Validation
  2. Then click the Add Rule button
  3. Under “Apply to Range,” enter B2 because you are setting the rule for cell B2. If you want to apply the rule to B2:B10, enter B2:B10.
  4. Under “Criteria,” select “Custom formula is” from the drop-down list
  5. Copy-paste the above formula
  6. Go through the “Advanced Options” and click “Done”.

This way, you can use the ISTEXT function in data validation to prevent non-text entries in a cell or cell range.

Note: To remove the data validation rule, navigate to the cell or any cell in the cell range and click on Data > Data Validation. Click the rule, then click the “Remove Rule” button.

ISTEXT Function in Conditional Formatting

Similarly to data validation, you can use the ISTEXT function in conditional formatting.

To highlight whether the entry in cell B2 is text, you can utilize the earlier formula, i.e., =ISTEXT(B2), within conditional formatting.

Steps:

  1. Click Format > Conditional Formatting
  2. Under “Apply to Range,” enter B2 or B2:B10 (the cell or cell range beginning from cell B2 that you want to highlight)
  3. Under “Format Rules,” select “Custom formula is”.
  4. Copy-paste the above formula.
  5. Select a formatting style and click “Done”.

Note: To remove the highlighting rule, navigate to the cell or any cell within the cell range and click on Format > Conditional Formatting. Hover your mouse pointer over the rule, then click the Trash can icon.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

More like this

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.