Use Data Validation in Excel to Allow Text Only

  • Post author:
  • Post category:Uncategorized

We can use the ISTEXT function to allow only text in a cell. This data validation formula will make excel allow only the kind of data we want. In a few steps below, we will learn how to use this formula.

Figure 1: Result of applying the ISTEXT function

General Formula

=ISTEXT(A1)

Formula

=ISTEXT(D4)

Setting up the Data

  • We will set up our data titled Coursework in Column B
  • The Scores for each course will be entered in Column C
  • Column D is where we will introduce the formula to return the result titled Remark.

Figure 2: Setting up the Data

Validate Data to Allow Text Only

  • We will highlight the range D4:D9 to be validated
  • We will go to the Data Tab at the top of the excel sheet
  • We will click on “Data Validation,”
  • In the Allow field, we will select custom and insert the formula: =ISTEXT(D4)

Figure 3: Applying the Data Validation function

  • In the Message tab, we will write the error title and error message we wish to see.

Figure 4: Input the Error text message

  • Now, we will click “OK” to apply
  • We will click Cell D4 and insert the text we want to see (in this case, “Credit”)

Figure 5: If text, then return TRUE

  • In Cell D5, we enter a value, and it returns with the error message.

Figure 6: Entered Value is not a Text

Figure 7: Final Sheet with Text

Explanation

=ISTEXT(D4)

We use the Data validation function and to restrict the kind of data we want in a cell when a user adds or wishes to change the cell value. The ISTEXT function returns TRUE if the value is text and FALSE if not. Because of this, a text will pass the validation, but numbers and formulas will not.

Notes

  • We should ensure that the “ignore blank” is clicked
  • We  can apply the Data validation window minus the input message
  • There are many other Data validation functions apart from ISTEXT function
  • In the formula field of the data validation window, we can also specify the kind of texts we wish to allow.  We should also separate each text with a semicolon or comma depending on the version of Excel we are using.