validation - Excel: how ensure cells contain numbers 1-6 or comma separated numbers 1-6?

Question

I have question and answer spreadsheets with up to a couple hundred rows in them. One column in each row indicates a correct answer by entering a number from 1 to 6 or multiple answers separated by commas like 1,3,5. I'd like to enforce this rule on every row and not allow input of anything else.

I find can't a way to use Custom Data Validation and am not sure how to use a function in each row.

Answer 1

• Select your range of interest, for example A1:A10

• Add the following formula as a custom data validation:

  =IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[preceding::*=.  or .*0!=0 or .*1>6 or .*1<1]"),"")=""
  

You'd need Excel2013 onwards but the formula will validate the string value to have:

• No duplicate values.
• No values other than numbers.
• No values above 6.
• No values below 1.
• Only comma will work as a valid seperator.

The A1 in the formula is relative so validation for A10 will work just the same way but then for A10 obviously.