Quote:
Originally Posted by lukin
Hi all, hoping someone can help me with a little trouble I'm having.
Order numbers in our system always begin with a letter "O" followed by 7 numbers (eg O6277305)
I have a sheet where users enter order numbers and I want to use custom data validation on this column so that you get an error if:
a) It doesn't begin with the letter "O"
b) It doesn't contain 8 characters
c) It contains any spaces
I have figured out how to do each of these by themselves (as below) but I can't seem to get them to work all together.
a) =LEFT(B3,1)="O"
b) =LEN(B4)=8
c) =B5=TRIM(B5)
I have tried the below, which data validation seems to accept is a legitimate formula but I don't get an error when entering something that breaks the rules:
=AND(LEFT(B3,1)="O",LEN(B4)=8,B5=TRIM(B5))
Is anybody able to give some advice on what I might be doing wrong?
Many thanks,
Luke
|
Hi Luke,
Using =AND() in this formula means the validation is dependent on all three conditions being met. So if you put in a reference that starts with a Z, is only 4 characters long and one of those is a space, the validation error message will kick in.
Try using =OR() rather than =AND()