Data Validation - Numbers only (can be multiple)

[surkdidat]

How do I have Data Validation that can only contain numbers (no text, punctuation etc). I have tried ISNUMBER, however, the field can contain two separate numbers i.e. 33 34 can be a valid entry, but 33, 34 or 34 & 35 should not be accepted

 

[Yongle]

How about

=ISNUMBER(VALUE(SUBSTITUTE(A1," ","")))

 

[tyija1995]

Hey there,

Try this Data Validation in cell A1 for example:
Allow: Custom
Formula: =ISNUMBER(SUBSTITUTE(A1," ","")+0)

 

[Yongle]

If only ONE space is permitted between each number, try this data validation custom formula

=AND(ISNUMBER(VALUE(SUBSTITUTE(A1," ",""))),LEN(A1)=LEN(SUBSTITUTE(A1," ","")))

 

[surkdidat]

Excellent, thank you! :D

How about

=ISNUMBER(VALUE(SUBSTITUTE(A1," ","")))

 

[tyija1995]

Just did some further tests on this and it won't currently like if your number resembles that of a number using comma format, for example try in the cell: 345, 678
It will allow that because it sees it as a number (it uses comma separation which is normal)
To get around this try this adapted formula:
=IF(ISNUMBER(SEARCH(",",A1)),FALSE,ISNUMBER(SUBSTITUTE(A1," ","")+0))

Now if you try 345, 678 in the cell it won't allow it!

 

[Yongle]

@tyija1995 - you are correct