CODEWITHSUNDEEP
excelvalidationexcel-formula
Velocedge
Velocedge

Reputation: 1455

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?

Upvotes: 0

Views: 392

Answers (1)

JvdV
JvdV

Reputation: 75900

Try:

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

Upvotes: 1

Related Questions