Data validation - accept "-" and numbers as input

**2001jesper** · 11-05-2013, 06:42 AM

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** · 11-05-2013, 06:58 AM

Hi,

Use a Custom Data Validation with this formula:

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

Regards

**2001jesper** · 11-05-2013, 07:08 AM

Perfect, work like a champ

Thax

**ajryan88** · 11-05-2013, 07:11 AM

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:

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** · 11-05-2013, 07:11 AM

Trumped me again XOR LX :P

**XOR LX** · 11-05-2013, 07:30 AM

Haha! Sorry!

Data validation - accept "-" and numbers as input

LinkBack

Thread Tools

Rate This Thread

Display

Data validation - accept "-" and numbers as input

Re: Data validation - accept "-" and numbers as input

Re: Data validation - accept "-" and numbers as input

Re: Data validation - accept "-" and numbers as input

Re: Data validation - accept "-" and numbers as input

Re: Data validation - accept "-" and numbers as input

Thread Information

Users Browsing this Thread

Similar Threads

Help Toggling "Circle Invalid Data" & "Clear Validation Circles" via a single button

[SOLVED] Data validation: allow entry into a cell if other three cells have "X", "Y" and "Z"?

[SOLVED] Manually fill up 1 or (some) cells with just "BFW"or"BF" in data validation

[SOLVED] Userform to accept input from textbox after "return" is pressed

Enter Data on "Main" or "Input" Sheet and Copy to One of Many Other Sheets

Bookmarks

Bookmarks

Posting Permissions