Data validation - accept "-" and numbers as input

[2001jesper]

Hi,

i have a small data validation issue


I need the cell to only accept "-" and whole numbers


I will appriciate any help on this matter

Thank you in advance

[XOR LX]

Hi,

Use a Custom Data Validation with this formula:

=IF(ISNUMBER(A1),A1=INT(A1),A1="-")

Regards

[2001jesper]

Perfect, work like a champ

Thax

[ajryan88]

Hi,

Assuming the cell that you want to validate is cell G5, select G5 and then go to Data Validation --> Allow Custom --> enter this formula:

Please Login or Register  to view this content.

Broken down, this formula says that G5:
1. Is exactly length 1 AND contains a "-" (and hence G5 is exactly a "-"); or
2. The truncated value of G5 is the same as the value of G5 (thus allowing only whole numbers)

NB: The IFERROR is required in the second argument of the OR function as a "-" results in a #VALUE error for this argument, which results in a #VALUE for the entire formula, resulting in the "-" not being allowed to be entered.

Hope this helps

[ajryan88]

Trumped me again XOR LX :P

[XOR LX]

Haha! Sorry!