Validate text which contain specific substring using data validation excel

In this article, we will learn How to validate text entries which contain specific substring using data validation in excel 2016.
Data validation tool restricts the user to input data to ensure its accuracy and consistency in Excel. It helps in the consistency of the data. It guides the user to input the type of option which is necessary for your worksheet.
Validation of text entries which contain specific substring is done with the use of Excel formula. Following steps will be a guide, how to validate text entries having specific criteria.

Her we have a task that only allow text entries containing specific substring. So we need to check some conditions for the same.

• Find the substring in text.
• Checks the result and returns TRUE or FALSE.

The use of functions and tool will help us get through the situation.

• Data validation tool
• FIND function / SEARCH function
• ISNUMBER function

We will construct a formula out of it. First the text would be checked for the specific substring. For that the FIND function returns the position of substring in text or returns error if not found. But Data Validation tool in excel only understand TRUE or FALSE. So another function, ISNUMBER function checks if it number then returns TRUE or else it returns FALSE if error.
Use the formula

Explanation:

1. FIND function returns the position of substring in text or returns error if not found.
2. ISNUMBER function checks if FIND function returned number or error.
3. ISNUMBER function returns TRUE if number or FALSE if error.

Let's get this through with this via using the formula using.

Here we have a list of Department and we need to fill the department ID manually. ID must have a specific substring "DT".

We will use the data validation attribute of excel. Follow the steps to do the same:

• • Select the cells where data validation need to apply.
  • Click Data > Data Validation option from the menu.

• • The Data validation dailog box appears in front.

• In the Settings option, Select the Custom option from the Allow list.
• A formula box appears under the same option.
• Use the formula in the formula box.

Explanation:

• FIND function returns the position of substring in text or returns error if not found.
• ISNUMBER function checks if FIND function returned number or error.
• ISNUMBER function returns TRUE if number or FALSE if error.

FIll the details as shown below and Click OK

Now when I enter the value as shown below. A default error generates.

As you can see an error is generated "This value doesn't match the data validation restrictions defined for this cell".
But now the problem is how the user know what restrictions are there and what type of value can the cell accept.
So for this, we will customize some changes as shown below.

In Input Message option, type the details under Title & Input Message box as shown above.

In Error alert option, type the details under Title & Error Message box as shown above.

Click Ok.

A message is displayed when the cell is selected And an error message is displayed as shown below.

Now fill the right answers, to complete the table.

To copy the data validation to the adjacent cells use the drag down or drag right option in excel.

Now our table is complete. Now its user friendly and informative as well.

Hope you understood how to Validate text which contain specific substring using data validation in Excel. Explore more articles on Data Validation here.  Please write your queries in the comment box below. We will help you.

Related Articles:

Data Validation in Excel : restricts user to input value manually in Excel. 

Way to use Vlookup function in Data Validation : allow values from the vlookup table in Excel.

Restrict Dates using Data Validation : allow dates in the cell which lays within Excel date format.

How to give the error messages in Data Validation : customize your data validation cell with error messages.

Create Drop Down Lists in Excel using Data Validation : restricts user to input data manually. 

Popular Articles

50 Excel Shortcut to Increase Your Productivity

Edit a dropdown list

Absolute reference in Excel

If with conditional formatting

If with wildcards

Vlookup by date

Join first and last name in excel

Count cells which match either A or B