## 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.