Excel data validation by combining multiple conditions

1
votes

Can i do excel validation formulas with multiple conditions? I have one column where i want 1) unique values 2) All the values have to be 10 characters(leading zeroes allowed) 3) All have to be numbers

I can get unique values with the below formula:

=COUNTIF($G:$G,G2)=1

How do I add the other two conditions?

If i set the type as number, the leading zeroes are not displayed.

2
excelexcel-formula

2 Answers

2
votes

Formula =AND(COUNTIF(G:G,G2)=1,LEN(G2)=10,ISNUMBER(VALUE(G2)))

  1. COUNTIF(G:G,G2)=1 checks the unique condition.
  2. LEN(G2)=10 checks the length, including leading zeros i.e., number stored in text format.
  3. ISNUMBER(VALUE(G2)) checks the value of the cell.

AND condition ensures all the above are met.

0
votes

Assuming you're only dealing with integers:

=AND(COUNTIF($G:$G, G2)=1, IFERROR(LEN(TEXT(G2, "0000000000"))=10, FALSE))