Just as you can use Excel for many other things, you can also use it to test what a Data validation must not contain. In this post, we’ll look at how to test what data validation must not contain using a formula approach in Excel.

Data validation must not contain

Formula

=SUMPRODUCT(--ISNUMBER(SEARCH(list, column to be validated)))=0

Explanation

To prohibit inputs having one of several things, you can simply employ data validation rule with a formula based on the “SEARCH” function.

Example 1

Figure 1. Test data validation must not contain

In figure 1 above, the data validation used in cell A4:A8 is:

=SUMPRODUCT(--ISNUMBER(SEARCH(list, A4)))=0

The data validation rules become active when a user changes or add a new cell value.

Step 1:

The formula applies the SEARCH function to check a user’s input for each of the values in the named range “list”. When a value from “list” is found, the SEARCH function returns the location of the value as an integer. If otherwise, it returns an error.

Step 2:

The “ISNUMBER” function converts integers and errors to TRUE and ERRORS respectively, while the double negative operator converts the TRUE to 1’s and FALSE to 0’s. Since “list” contains 4 values, we got the array below :
{0;0;0;0}

Step 3:

SUMPRODUCT adds up the items in the array and the outcome tests against zero. If SUMPRODUCT returns zero, the validation succeeds. If other numbers in the list are returned, then the formula returns FALSE and the validation fails.