How to Use ISTEXT in Excel? With 3 Different Methods
(Note: This guide on how to use ISTEXT is suitable for all Excel versions including Office 365)
The ISTEXT in Excel is categorized under Information functions in the Formulas tab. It returns TRUE if the given value is a text, and FALSE if the cell contains any other value or is empty. You can use the ISTEXT function to check if a cell contains a text, numeric, date, or any other value.
You’ll Learn:
- Syntax
- Parameters
- Method 1 – ISTEXT in the Formula Bar
- Method 2 – Using Data Validation
- Method 3 – Along with the IF Function
- To Remember
Related Reads:
How to Use Excel Split Screen? 3 Simple Ways
How to Create a Venn Diagram in Excel? 2 Easy Ways
How to Create a Step Chart in Excel?
Syntax
| =ISTEXT(value) |
Parameters
The ISTEXT function takes one argument, a value which can be a number, text, cell reference, a formula, an error, or a hardcoded value.
Method 1 – ISTEXT in the Formula Bar
The ISTEXT function can be entered as a formula in the worksheet. In the example below, the table consists of different values in column A and the function is applied in column B. The ISTEXT in Excel turns TRUE only for the text values and FALSE for other values such as time, error message, date, number, and an empty cell.
Method 2 – Using Data Validation
If you wish that only text has to be entered in a particular cell, use ISTEXT along with data validation to get the desired results. In the Excel ribbon, choose the Data tab. You will find the Data Tools ribbon unfolding.
The Data Tools ribbon has the following options:
- Text to Columns
- Flash Fill
- Remove Duplicates
- Data Validation
- Consolidation
- Relationships
- Go to the Power Pivot window
Click on the Data Validation option and pick from a list of rules to limit the type of data that can be entered in a cell.
On the Settings tab -> Validation criteria -> Allow, select an option:
- Whole number – accepts only whole numbers.
- Decimal – accepts only decimal numbers.
- List – picks data from the drop-down list.
- Date – restricts the cell to accept only dates.
- Time – restricts the cell to accept only time.
- Text length – restricts the length of the text.
- Custom – to customize the formula.
Apply the Formula
Apply data validation to the cells B2:B5 where you want only the text to be entered. First, select the columns B2:B5, then click on Data -> Data Tools -> Data Validation. In the validation criteria, click Custom in the Allow field and enter the formula as =ISTEXT(A2). Drag the formula to the cells downwards where you will have values to validate.
Set the Input Message
Select the Input Message tab and customize a message that users will see when entering data. Enable the ‘Show input message when the cell has selected’ checkbox to display the message when the user selects or hovers over the selected cell(s).
In the example above, enter a message that has to be displayed in the cell where the formula is applied. When you hover the mouse over the cell, the input message is displayed to alert the user to enter only the text value.
Set Error Alert
Select the Error Alert tab and enable the Show error alert after invalid data is entered. Choose the Style and customize the error message and then select OK.
In the example below, you see that if you enter the text, the result is printed if it is text value or it returns an error message if any other value is entered.
Data validation rules are executed when a user adds, deletes, or changes a value in the cell. The cell references are checked to find any violation of the rules defined in data validation.
Suggested Reads:
Short Date Format in Excel – 3 Different Methods
How to Graph a Function in Excel? 2 Easy Ways
How to use EDATE in Excel? 11 Easy Examples
Method 3 – Along with the IF Function
ISTEXT in Excel is used with the IF function to ensure only text values are entered in the table. This is done by nesting the ISTEXT function within the IF condition to ensure that the user fills only text values.
The formula is, =IF(B3=””,” Enter Your Name”, IF(ISTEXT(B2),” OK”,” Text only”)), as shown below:
The formula is applied in cell C2 and dragged until the end of the table. The formula parses the value in cell B2 and checks whether the entered value is text or not. If the condition is true, that is a text is entered, then the message “OK” is displayed.
If a number or any other value is entered, the formula prompts the user to enter either yes/no and nothing other than that.
To Remember
- The ISTEXT in Excel will return FALSE even for formula errors such as #VALUE!, #NULL!, #NUM, #REF, etc.
- ISTEXT belongs to the IS family of functions/ Each of these functions, collectively referred to as the IS functions, checks the specified value and returns TRUE or FALSE depending on the outcome.
Also Reads:
How to Use AVERAGEIF in Excel? With 5 Different Criteria
How to Enable Excel Dark Mode? 2 Easy Methods
How to Calculate Factorial in Excel? Along with 2 Easy Examples
Closing Thoughts
The ISTEXT in Excel is used in form design to check whether a field allows only text values in the text box. The users can be alerted that the text box accepts only text by using the Error Alert message.
In this article, we saw how to apply ISTEXT to a group of cells using three different methods. When it is used with the IF function, you can customize the error message and the prompt message.
If you need more high-quality Excel Guides, please check out our free Excel resources center. Simon Sez IT has been teaching Excel for ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
