< Go Back

Data validation allow text only

Data validation makes it possible to customize the kind of data that a cell should allow. To make a cell accept only text, there are some steps that must be followed.
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

=ISTEXT(A1)

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

  1. Go to your excel sheet and make a data table containing the cell you want to validate
  2. Highlight the cells
  3. Go to the Excel Data tab and click on Data Validation
  4. On the settings tab in the Data Validation window, fill the validation criteria: – In the ‘Allow’ field, choose custom
  5. In the formula field, enter the ISTEXT function formula
  6. Go to the input message tab still in the data validation window
  7. Input your desired error message title and a main input message
  8. Click ok.
  9. You can now test the validation by entering a number

Example

If you need to validate a column so that only text can be written in it, do the following:

  1. Create the data table

Figure 1 – Showing data to be validated

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

  1. Click on Data Validation, and in the Allow field, select custom and then insert the formula field, enter the formula: =ISTEXT(D2)

Figure 3 – Showing the data validation procedure

  1. On the Input Message tab, write the error title and the error message you want to see.

Figure 4 – Showing the input message entry

  1. Result just before error message was set

Figure 5 – Showing the validation attempt after writing 65%

  1.     The result after the error message was set

Figure 6 – Showing the ‘Not a text?’ error message

Notes

  • 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
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar