On Monday, April 23, 2012 11:14:20 PM UTC-7, lukin wrote:
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









--

lukin