Data validation makes it possible to customize the kind of data that a cell should allow. To make a cell accept only text in Excel, there are some steps that must be followed.
Data validation to allow text only
Custom data validation is made possible using the ISTEXT function.
When the data validation formula is applied to a cell, the data rules are triggered once the cell value is altered.
Data validation is necessary for several reasons. It would be to allow only a specific set of texts to be written in a cell, or to prevent every entry that is not text from being allowed in a cell.
In this post, you will find out how to validate a set of cells by allowing only texts in them.
Formula using ISTEXT
How to Validate Data to Allow Text Only
Data validation works using the ISTEXT function. If you enter text in a cell, the ISTEXT function will return TRUE after confirming that the value is really text, and any other type of value will return FALSE.
The parameters in the ISTEXT data validation include function
- ISTEXT: This is the function that does the computation.
- A1: This refers to each cell where the data should be validated to text.
The following steps would help you validate a set of cells to allow only text
- Go to your Excel sheet and make a data table containing the cell you want to validate
- Highlight the cells
- Go to the Excel Data tab and click on Data Validation
- On the Settings tab in the Data Validation window, fill the validation criteria: – In the ‘Allow’ field, choose Custom
- In the formula field, enter the ISTEXT function formula
- Go to the Input Message tab still in the data validation window
- Input your desired error message title and a main input message
- Click ok.
- You can now test the validation by entering a number
If you need to validate a column so that only text can be written in it, do the following:
- Create the data table
Figure 1 – Showing data to be validated
- Highlight the range to be validated and go to Data tab at the top of the excel sheet
Figure 2 – Showing the highlighted section for data validation.
- Click on Data Validation, and in the Allow field, select custom and then insert the formula field, enter the formula:
Figure 3 – Showing the data validation procedure
- On the Input Message tab, write the error title and the error message you want to see.
Figure 4 – Showing the input message entry
- Result just before error message was set
Figure 5 – Showing the validation attempt after writing 65%
- The result after the error message was set
Figure 6 – Showing the ‘Not a text?’ error message
- Apart from TEXTIF, some other functions and methods are able to validate data to allow text only.
- You can also validate data to allow only a certain set of text, e.g., Yes or No.
- You can use the data validation window without necessarily using the input message tab
- On the data validation window, ensure that the ‘ignore blank’ button is clicked.
- In the formula field in the data validation window, you can specify the specific texts that should be allowed in each cell. They should be separated by a semicolon or a comma depending on the version of excel you are using
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.