< Go Back

Data validation must contain specific text

In Excel, you will definitely have numerical data to be recorded, in that case, if you want to keep a check on the data type of entries made in the cell, you must enable data validation for it. To allow only entries that contain some specific text, data validation option must be changed accordingly to allow only that text.

Excel Data Validation function is used to restrict the type of data that is entered to a cell. Any cell can be formatted to receive an input entry of some specific type. For example, if it is required that only those entries are permissible in the cell which contains some specified text content, otherwise these entries will be denied. Then the data validation setting can be adjusted to allow those entries containing some specific text only.

Formula

=ISNUMBER(FIND("txt",Xn))

Parameters of DSTDEVP function

FIND function: ISNUMBER check is applied on FIND function. This function requires two parameters in this perspective, which are as follows:

txt: The text which is mandatorily required to be a part of the entry.

Xn: The cell number to which the data validation option is being applied e.g. A1

Explanation

Microsoft Excel has many built-in data validation rules for cell entries. This article explains that how to set the data validation to allow only entries in the cells, which have some text or specific string with them. To allow such entries in a cell, you can use the data validation option by setting it to the custom option based on providing the ISNUMBER function for that specific cell.

In the example presented below, data validation is applied to some cells B3:B9.

Example

In this example, some hypothetical products and their costs are entered in an Excel spreadsheet. Now it is required by the user that the cost column only accepts values which are numbers and have a post-fix text that represents the acronym of US Dollar currency i.e. USD. In that case, data validation rules must be set for these cells. Data validation check is being made, each time the user adds or changes a value to that cell.

To set the data validation checking that the text must contain some specific text only, you should follow the following steps.

First of all, select the cells or range of the cells to which data validation must contain specific text option is to be applied.

Figure 1. Selection of data

After that, click on the Data button in the menu bar. From the pop-down menu, go to the Data validation settings as shown below.

Figure 2. Opening the Data Validation pop-up window

When you click on the Data validation option, a pop-up window will appear. In that window, go to the setting tab and select the option “Custom” from the drop-down menu available for “Allow” option. As shown below:

Figure 3. Setting the Data validation criteria to custom

To configure the cells such that data validation must contain some specific text, use the formula =ISNUMBER(FIND(“USD”,C3)) in the “Formula” option.

Figure 4. Setting the Data validation to must contain “USD” text in the entries

Now, if any value is entered into the cell that does not have USD text in it, the entry will be denied generating a type validation error, as shown below:

Figure 5. Data validation must contain some specific text, otherwise, an error will be generated.

The permissible entries are 20 USD, 12 USD, 10 USD and so on.

Note

  • ISNUMBER function is replaced by ISERROR function if the is required not to be valid in case it is containing the specific text part in it.
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

Leave a Comment

avatar