# Excel data validation for decimals, but without a range?

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?

-
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

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.

### 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.

-
+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
``````
-