Go Back

How to Perform Data Validation in Excel with the ISTEXT Function

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:

  1. Select cell C2 by clicking on it. Drag the selection till C7 with your mouse.
  2. On the Excel ribbon, click on Data > Data Validation.
  3. In the window that pops up, click on the Allow drop-down and select Custom.  
  4. In the Formula box, assign the formula  =ISTEXT(C2).
  5. 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. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I used Data validation to only allow numbers in cell. so it works when I type a text and it throws an error. But when I copy paste text it still allows me to do so. how do I stop pasting text into cells.
Solved by S. W. in 20 mins
Is there a way to allow users to access and put data in an excel cell but not allow them to change anything but the text?
Solved by I. E. in 18 mins
allow only numbers and hyphen in particular cell
Solved by V. C. in 30 mins

Leave a Comment