Search code examples
excelvalidationexcel-formula

Excel: Custom Data Validation for numbers 0-9 or comma separated values 0-9

I had asked a similar question in: Excel: how ensure cells contain numbers 1-6 or comma separated numbers 1-6?

JvdV gave me a great answer and formula for numbers 1-6 or comma separated.

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

I would have thought it would be easy to modify but obviously, I don't understand the syntax. What is the syntax to allow only the numbers 0-9 or those values separated by commas?

Solution

  • Try:

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