This guide covers everything you need to know about the Google Sheets ISTEXT function, including its definition, syntax, use cases, and how to use it.
What is the ISTEXT Function? How Does It Work?
The ISTEXT function in Google Sheets checks whether a certain value is text. If the input value is a text or a reference to a cell containing a text value, ISTEXT will return TRUE. On the other hand, if the input value is not text or a reference to a cell that does not contain a text value, the function will return FALSE.
One of the ISTEXT function’s main purposes is to identify cells containing text in a spreadsheet. This can be particularly useful when dealing with large datasets where you need to sort or filter data based on whether cells contain text. ISTEXT can also be used in conjunction with other logical functions, most often with the IF function in conditional statements, to automate certain tasks or processes in your sheet.
When supplied with an empty string, such as ISTEXT(“”), the function will return TRUE, indicating that an empty string is considered a text value. However, when referencing a blank cell, the function will return FALSE. This highlights a distinct difference in how Google Sheets treats empty strings and blank cells.
ISTEXT Syntax
The syntax and arguments for the function are as follows:
ISTEXT(value)
Where:
- value: This is the value that you want to verify as text.
ISTEXT will return TRUE if the value is text or a reference to a cell containing text. If the value is anything other than text, the function will return FALSE.
Here are some important usage notes related to the syntax and arguments:
- The ISTEXT function only takes one argument. If you supply more than one argument or no arguments at all, Google Sheets will display an error message.
- The argument for the ISTEXT function can be a cell reference (e.g., A2), a text string enclosed in quotation marks (e.g., “hello”), or a formula that returns a text value.
- The ISTEXT function is case-insensitive. It does not differentiate between uppercase and lowercase letters. For example, ISTEXT(“HELLO”) and ISTEXT(“hello”) will both return TRUE.
- The ISTEXT function does not consider numbers, logical values (TRUE or FALSE), error values, or dates and times as text. If you use such values as the argument, the function will return FALSE.
Examples of How to Use the ISTEXT Function
Here are some practical examples of how to use the ISTEXT function in Google Sheets:
Example #1: Checking if a Cell Contains Text
Suppose you have a cell A1 with the value “100” and want to check whether it contains text. You can use the ISTEXT function like this:
=ISTEXT(A1)
This function will return FALSE because “100” is a number, not text.
Example #2: Confirming Text in a List
Let’s say you have a list of items in column A from A2 to A6: “Apple”, “Banana”, “100”, “200”, “Orange”. You want to check if each item is text. In cell B2, enter the formula:
=ISTEXT(A2)
Then, drag the fill handle (the small square at the bottom-right of the cell) down to copy this formula to the other cells in column B. The function will return TRUE for “Apple”, “Banana”, and “Orange”, and FALSE for “100” and “200”.
Example #3: Checking for Text in a Date
If you have a date in a cell, say A1, and want to check if it’s text, you can use the ISTEXT function. Let’s say A1 contains “2022-01-01”. Enter the formula:
=ISTEXT(A1)
The function will return FALSE because even though the date might look like text, Google Sheets recognizes it as a date, not text.
Why Is ISTEXT Not Working? Troubleshooting Common Errors
If you’re struggling to get the ISTEXT function to work in Google Sheets, there’s a chance you may be encountering some common errors. Here, we’ll walk you through these issues, their causes, and how to resolve them.
#VALUE! Error
Cause: This error typically occurs when the ISTEXT function is given references that it cannot process. For example, you might be trying to reference a cell that does not exist or a range of cells instead of a single cell.
Solution: Ensure that the cell you are referencing exists and that you are only referencing a single cell. If you need to check multiple cells, you may need to use an array formula or another method.
#N/A Error
Cause: This error is generally caused when the cell you are trying to reference is empty. This can happen if you’ve deleted the content of the cell or if you’re referencing a cell that has not yet been filled.
Solution: Double-check the cell you are referencing. If it’s empty, either enter the necessary data or adjust your formula to reference a different cell.
#REF! Error
Cause: This error occurs when a cell that your formula is referencing has been deleted. This is common when you delete a column or row that contains a cell used in your formula.
Solution: Adjust your formula to reference a cell that has not been deleted. Alternatively, if the deleted cell is necessary for your calculation, you may need to undo your deletion or re-input the necessary data.
#NAME? Error
Cause: This error is usually caused by a typo in your formula. Google Sheets cannot recognize the function or name you’ve entered, and therefore cannot execute the formula.
Solution: Double-check your formula for any spelling or syntax errors. Make sure that you’ve correctly spelled “ISTEXT” and that you’re using the correct syntax (i.e., =ISTEXT(cell_reference)).
#NUM! Error
Cause: This error occurs when the ISTEXT function is used with a cell that contains a number. The ISTEXT function can only be used with text values.
Solution: Ensure that the cell you are using with the ISTEXT function contains text. If it contains a number, consider converting the number to text using the TEXT function.
Circular Reference Error
Cause: This error occurs when the cell you’re trying to reference in your formula refers back to the cell with the formula. This creates a loop that Google Sheets cannot resolve.
Solution: Adjust your formula to avoid circular references. Make sure that the cell you’re referencing does not contain a formula that references back to the original cell.
Using ISTEXT With Other Google Sheets Functions
Combining ISTEXT with other Google Sheets functions can allow you to perform more complex tasks and data manipulations. Here are a few examples of how ISTEXT can be used alongside other functions.
With IF
Usage: The IF function in Google Sheets is used to create conditional statements where if a certain condition is met, a specific action will be taken. When combined with ISTEXT, you can create a formula that performs a specific action if a cell contains text.
Example: =IF(ISTEXT(A1), “This is text”, “This is not text”)
In this example, if cell A1 contains text, the formula will result in “This is text”. If cell A1 does not contain text, the formula will result in “This is not text”.
With COUNTIF
Usage: The COUNTIF function in Google Sheets counts the number of cells in a range that meet a certain condition. When combined with ISTEXT, you can count the number of cells in a range that contain text.
Example: =COUNTIF(A1:A10, ISTEXT)
In this example, the formula will count the number of cells in the range A1:A10 that contain text.
With ARRAYFORMULA
Usage: The ARRAYFORMULA function in Google Sheets is used to apply a formula to an entire column or row of cells. When combined with ISTEXT, you can apply a text check to an entire column or row.
Example: =ARRAYFORMULA(ISTEXT(A1:A100))
This will return an array of TRUE and FALSE values corresponding to whether each cell in the range A1:A100 contains text or not.
With SUMPRODUCT
Usage: The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. When combined with ISTEXT, you can use it to count the number of text cells in a range.
Example: =SUMPRODUCT(–ISTEXT(A1:A10))
This formula returns the number of cells in the range A1:A10 that contain text. The double negative (–) is used to convert TRUE and FALSE values returned by ISTEXT to 1 and 0, respectively, which can be used in arithmetic operations.
These are just a few examples of how ISTEXT can be combined with other Google Sheets functions to perform more complex tasks. Understanding how these functions work together allows you to create powerful formulas to manipulate and analyze your data.
For more details on the ISTEXT function, check out the official documentation at the Google Docs Editors Help Center.
