Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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
up vote 3 down vote accepted

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.

share|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

I think this custom formula meets your needs:

=INT(A1)=A1
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .