up vote 0 down vote favorite
share [g+] share [fb]

I want to use the Data Validation in Excel to prevent users from entering a non-decimal value, but I want to allow them to enter any decimal value, however you are forced to provide a range or restriction. Is there way to force a decimal, but not have to put a restrictions on the range of values?

link|improve this question
69% accept rate
3  
Can you make the range so large that it doesn't matter? – bwall Jun 9 '11 at 20:33
Kind of what I've done in the meanwhile. I selected "Value not equal to" and put 999999999999. Then the odds of it ever being exactly that value are pretty small I supposed, just feels a bit hackish. – AaronLS Jun 9 '11 at 21:11
this could be done with macros. if you're interested let me know and I'll throw together an example – Xantec Jun 9 '11 at 21:21
feedback

2 Answers

up vote 2 down vote

Try these:

Method 1: Checking for any number

  • Highlight the cell you wish to apply validation to. For this example, let's assume you want the user to enter a value in A1.
  • Go to Data > Data Validation
  • Set to Custom

  • Enter the formula below.

    =ISNUMBER(A1)

where A1 is the cell you are validating. Do not include $ so you can copy/paste the validation to other cells.

enter image description here

Method 2: Checking for decimal pts

Create a blank column beside your data. For this example, you want users to enter values in column A, so we'll create a blank column in B.

In cell B1, enter this formula and drag it down.

=AND(ISNUMBER(A1), FIND(".",TEXT(A1,"#.#"))<>LEN(TEXT(A1,"#.#")))

Credits to DMA57361 for the tip

  • Highlight A1.

  • Go to Data > Validation

  • Set to Custom and enter the formula below:

    =B1

  • Do Copy > Paste Special > Validation to other cells.

I'm sure there are other methods. This was all I could muster in a few mins.

link|improve this answer
1  
+1, But note that you can greatly simplify your macro by letting the language do the string search for you with If (InStr(num, ".") > 0) Then. – DMA57361♦ Jun 10 '11 at 11:09
Also, you could probably pull off the "check for a decimal point" approach with an ugly formula instead of having to go to the level of a macro, for example =FIND(".",TEXT(A1,"#.#"))<>LEN(TEXT(A1,"#.#")) seems to work. – DMA57361♦ Jun 10 '11 at 11:17
@DMA57361 Thanks for the macro tip; I'll adjust the code. :D Also, I tried to do a formula for the Data Validation. Something along the lines of "=NOT(ISNA(FIND(".",A1)))" but for some reason I couldn't make it work. – Kaze Jun 10 '11 at 13:03
@Hidden, FIND returns #VALUE! when it fails, not #N/A, so ISNA() won't help here. Note that just doing a FIND on the . character would mean "word.word" would be falsely accepted - you have to check it's a valid number as well (in fact, that's probably a flaw in the macro and my previous suggestion as well). – DMA57361♦ Jun 10 '11 at 13:13
I added an ISNUMBER check. Problem is, Excel auto-converts "15.00" to "15" so whole numbers are ignored, even when the user types it in decimal form. – Kaze Jun 10 '11 at 13:30
show 3 more comments
feedback
up vote 0 down vote

I think this custom formula meets your needs:

=INT(A1)=A1
link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.