How to have Data Validation allow a certain letter and numbers

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Can someone please tell me how I can get Data Validation to only allow the
letter x as well as any number with 2 decimals in a cell.
Rob
 
Select Custom, enter this formula (assumes A1 is the active cell):
=OR(A1="x",AND(ISNUMBER(A1),A1<1,LEN(A1)=4))
len of 4 because when you enter .33, excel will enter 0.33.
HTH

Bob Umlas
Excel MVP
 
Assuming you enter something like: x.33 in A1
Use Custom Data Validation and enter (with Cell A1 Active):
=AND(LEFT(A1,1)="x",LEN(A1)=4)
HTH
 
Thanks for your reply JMay, but the requirement is that either an x OR a
number (allowing up to 2 decimals) be entered. Sorry if I was not clear.
Rob
 
Bob, Thanks for that but at the moment it's not quite doing it. There seems
to be a problem with the last part. Deleting that part and slightly
amending what's left I get...
=OR(E465="x",AND(ISNUMBER(E465),E465>0))

Which works well except that it doesn't limit the number input to a max of 2
decimals (obviously because I deleted the LEN(A1)=4) bit). But I don't know
how to fix that as that part won't allow me to enter any whole numbers or
numbers with only 1 decimal. I'm sure my earlier request wasn't too clear
so I'm also sure you can fix this for me, please.
In short, I need to be able to enter an x in the cell.... OR.... enter a
currency amount (which could be whole numbers, or numbers up to 2 dec.)
Rob
 
When the formulas start getting too complex for my brain, sometimes I'll just
write it as a worksheet formula:

=IF(A1="x",TRUE,IF(ISTEXT(A1),FALSE,IF(A1=--TEXT(A1,"0.00"),TRUE,FALSE)))

I can put it in an adjacent cell for ease of testing, too.
 
Bewdy Dave. Works great.
BTW, I do use that method in constructing formulas and I usually put part
formulas into separate cells and then combine them, but couldn't quite work
this one out. That double negative always catches me out.
Thanks!
Rob
 
Back
Top