How to Apply Multiple Data Validation in One Cell in Excel (3 Examples)

In this tutorial, we will demonstrate how to apply multiple data validation in one cell in excel. If you work regularly on Microsoft Excel it may sound very common to you to apply data validation in an excel cell. But, what if you need to apply multiple data validation in one cell? So, in this article, to answer this question we will show different examples of applying multiple data validation in one cell in excel.


Download Practice Workbook

You can download the practice workbook from here.


3 Examples to Apply Multiple Data Validation in One Cell in Excel

There is no limit to types of applying multiple data validation in one cell. Because we can combine any two or more conditions with data validation. So, to illustrate the process of applying multiple data validation we will demonstrate 3 different examples in this article.


1. Apply Multiple Data Validation in One Cell in Excel with Specific Criteria

In this method, we will set two specific criteria to apply multiple data validation in one cell in excel. We have the following screenshot of a dataset with two criteria. We will configure a data validation in cell E8 with the two given criteria. The validation will work like the value that we will insert in cell E8 has to be greater than 50 or has to be in the range (B8:B10). Any value other than these two conditions will show a warning that the value we want to enter doesn’t match the data validation.

From the following dataset, we can see the formulas in cells C5 & C9 to meet the criteria that we will use for validation.

Apply Multiple Data Validation in One Cell in Excel with Specific Criteria

So, let’s take a look at the steps to perform this action.

STEPS:

  • Firstly, combine the criteria formulas in a nested formula in cell C13 with AND Copy the nested formula from the formula bar but do not paste it anywhere.

Apply Multiple Data Validation in One Cell in Excel with Specific Criteria

  • Secondly, select cell E8.
  • Thirdly, go to Data > Data Tools > Data Validation > Data_Validation

Apply Multiple Data Validation in One Cell in Excel with Specific Criteria

  • The above action will open a new dialogue box named ‘Data Validation’.
  • Next, select the option Custom from the Allow drop-down menu.

Apply Multiple Data Validation in One Cell in Excel with Specific Criteria

  • Then, paste the validation formula in the Formula text box:
=AND(E8>$B$5,COUNTIF($B$8:$B$10,E8)=1)
  • Click on OK.

Apply Multiple Data Validation in One Cell in Excel with Specific Criteria

Here, the COUNTIF function checks if the input value matches with the range values (B8:B10). The AND function combines the two criteria.

  • After that, input value 40 in cell E8.

  • So, excel will give us a warning prompt box for the above value as it doesn’t match our ‘Criteria 1’. The value in cell E8 must be greater than 50.

  • Lastly, enter the text ‘Apple’ in cell E8. We can see that Excel accepts this value. This value meets the second criteria as it is in range (B8:B10).

Read More: Apply Custom Data Validation for Multiple Criteria in Excel (4 Examples)


2. Allow a Text and a Date between Two Dates by Applying Multiple Data Validation in One Cell

In this example, we will set two criteria for data validation. The criteria will only allow a specific text and a date between two dates in a cell. In the following dataset, we have word Excel in ‘Criteria 1’ and two dates ‘1-03-22’ & ‘31-03-22’ in ‘Criteria 2’. We can see the formulas that give the conditions to meet the above criteria. We will use these formulas to apply multiple data validation in cell E8.

Allow a Text and a Date between Two Dates by Applying Multiple Data Validation in One Cell

Let’s see how we can do this example.

STEPS:

  • First, merge the criteria formulas in a nested formula with AND function in cell C12.
  • Next, copy the nested formula from the formula bar but don’t paste it anywhere.

Allow a Text and a Date between Two Dates by Applying Multiple Data Validation in One Cell

  • Then, open the data validation window.
  • Select the option Custom from the Allow drop-down. Insert the validation formula in the Formula text box:
=OR(E8=$B$5,AND(E8>=DATE(2022,03,1),E8<=DATE(2022,03,31)))
  • Click on OK.

Allow a Text and a Date between Two Dates by Applying Multiple Data Validation in One Cell

Here, the AND function checks if the input date is between ‘1-03-22’ & ‘31-03-22’ or not. The OR function combines the two criteria and gives output if anyone is true.

  • After that, enter the text Excel in cell E8. We can see that excel doesn’t show any warning prompt box. Because it matches the ‘Criteria 1’ of our data validation formula.

Allow a Text and a Date between Two Dates by Applying Multiple Data Validation in One Cell

  • Again, if we input the text Word in cell E8, it will show a warning prompt box. It’s happening because the text value Word doesn’t match any criteria.

  • Now, enter a date in cell E8 which from the date range ‘1-03-22’ to ‘31-03-22’. We can see excel accepts the value as it matches with ‘Criteria 2’.

  • Finally, write a date in cell E8 outside the date range of the validation formula. Excel will give us a warning prompt box that the given value doesn’t match any criteria of our validation formula.

Read More: How to Create Excel Drop Down List for Data Validation (8 Ways)


Similar Readings:


3. Use Multiple Data Validation in One Cell in Excel to Fix Starting Character and Length of a String

In the third example, we will use multiple data validation in one cell to start characters and the length of a string that we will input. We have the following two criteria in the given dataset. We will apply multiple data validation in cell C8. The data validation that we will apply in cell C9 will allow us to input data only that starts with the text ‘ISBN’ and ‘CODE’. The text that starts with text must be 8 characters and the text that starts with ‘CODE’ must be 10 characters. Otherwise, the validation formula will return a warning prompt box.

Use Multiple Data Validation in One Cell in Excel to Fix Starting Character and Length of a String

Let’s see the steps to do this example.

STEPS:

  • In the beginning, create the data validation formula in cell D5.that covers all the criteria.

Use Multiple Data Validation in One Cell in Excel to Fix Starting Character and Length of a String

  • Next, open the ‘Data validation’ dialogue box like the example-1. Select the option Custom from the Allow drop-down menu.
  • Then, insert the data validation formula in the Formula text box.
=OR(AND(LEFT(C9,4)="ISBN",LEN(C9)=8),AND(LEFT(C9,4)="CODE",LEN(C9)=10))
  • Click on OK.

Use Multiple Data Validation in One Cell in Excel to Fix Starting Character and Length of a String

Here, the LEFT function extracts 4 characters from the string. The LEN function calculates the length of the string. The OR function combines the two criteria and gives output if anyone is true.

  • After that, insert the value ‘ISBN-358’. The value starts with characters ‘ISBN’ and the length of the string is 8. We can see that excel accepts the value as it matches both criteria.

  • In the end, we will add two more characters with the previous input value. Excel shows a warning prompt box though it starts with text ‘ISBN’ which matches the first criteria. But the text contains 10 characters that don’t match the second criteria.

Read More: Create Data Validation Drop-Down List with Multiple Selection in Excel


Conclusion

In conclusion, this tutorial will give you a brief idea to apply multiple data validation in one cell. To put your skills to the test, utilize the sample worksheet that comes with this article. Please leave a comment below if you have any questions. Our team will try to reply to you as soon as possible. In the future, keep an eye out for more innovative Microsoft Excel solutions.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo