Excel has some very effective data validation tools. You can also add your own data validation rules using custom formulas. To allow text only, you need to use the ISTEXT function in excel for data validation. In this tutorial, you will learn how to use the ISTEXT function and go on to create your own data validation to allow text only using the ISTEXT function.
How to use ISTEXT Function in Excel for Data Validation
The ISTEXT function takes an argument and returns TRUE if it is text, and FALSE otherwise. The following example contains values of different types in column A. To figure out if the values are text:
- Select cell B2 with your mouse.
- Assign the formula
=IF(ISTEXT(A2),"Text","")to cell B2.
- Press Enter.
- Drag the formula to cells B2 to B7.
This will show Text if the values in column A are text values and an empty string otherwise.
Data Validation to Allow Text Only
Using the concepts of the previous example, you can create your own data validation ISTEXT function to allow text only. The next example focuses on student attendance data. Column A has the student names, column B has the dates. To allow only text values in column C which are the attendants:
- Select cell C2 by clicking on it. Drag the selection till C7 with your mouse.
- On the Excel ribbon, click on Data > Data Validation.
- In the window that pops up, click on the Allow drop-down and select Custom.
- In the Formula box, assign the formula
- Click Ok.
This will allow only text values in column C. If you try to enter other data types, Excel will generate an error. However, you can set your own custom error message to display. In order to do this:
- After finishing step 4, click on the Error Alert tab, on the Error Message box, type Input not a text value. Please enter a valid text value.
- Click Ok.
Every time you enter a value now that is not text, Excel will show this message.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.