Data Validation to allow specific text entry and prevent other errors

[sparky2205]

I am trying to use Data Validation to achieve the following:
Allow n/a and a valid decimal value
Prevent text other than n/a and errors like; .23.23 where the user accidentally enters a second decimal point

I have the following formula which works fine when I use it directly on a spreadsheet:

=AND(ISERROR(FIND("N/A",UPPER(B9))),((LEN(B9)-LEN(SUBSTITUTE(B9,".","")))<>1))

i.e. n/a returns FALSE, 3.51 returns FALSE, .23.23 returns TRUE

However, when I transfer this to a Data Validation rule:
n/a triggers the rule, 3.51 triggers the rule, .23.23 doesn't trigger the rule. The exact opposite behaviour to that directly in the spreadsheet and to what I need.

Any thoughts?

 

[etaf]

how about
=OR(ISNUMBER(A1),A1="N/A") - custom Data Validation rule

Book1
	A	B	C
1	0.22
2
3	.2.2		FALSE
4	0.222		TRUE
5	N/a		TRUE
6
Sheet1

Cell Formulas
Range	Formula
C3:C5	C3	=OR(ISNUMBER(A3),A3="N/A")

Cells with Data Validation
Cell	Allow	Criteria
A1	Custom	=OR(ISNUMBER(A1),A1="N/A")

 

[sparky2205]

That will still accept .23.23 as a number

 

[etaf]

doesnt for me

Book1
	A	B	C
1	0.22
2
3	.2.2		FALSE
4	.23.23		FALSE
5	N/a		TRUE
6
Sheet1

Cell Formulas
Range	Formula
C3:C5	C3	=OR(ISNUMBER(A3),A3="N/A")

Cells with Data Validation
Cell	Allow	Criteria
A1	Custom	=OR(ISNUMBER(A1),A1="N/A")

 

[sparky2205]

Yep, don't know what I did the first time I tried it, but it's working fine now.
Thanks for your help.

 

[etaf]

you are welcome