Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

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. –  Ellesa 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. –  Ellesa Jun 10 '11 at 13:30
show 3 more comments

I think this custom formula meets your needs:

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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