Field Validation for Length & Character Type

[Rob]

Hi,

I have a bunch of fields that I have validation on but there are two fields
that are so similar that I am having a problem trying to get them to be
uniquely validated.

both can only be 8 characters long but the first three characters are
letters and the last five are numbers. Range G5 needs to be validated to be
between ASA00000 & ASA99999. Range G30 needs to be validated to be between
AST00000 & AST99999. If I use the "Text Length" validation type then the
numbers can be swapped. I can't use the "Whole Number" validation because of
the first three characters. Is there a way to make a formula by using the
"Custom" validation that would work?

Thanks In Advance,
Rob

 

[Dave Peterson]

With A1 the activecell, this would get you pretty close:

=and(len(a1)=8,left(a1,3)="asa",isnumber(-right(a1,5)))

 

[Rob]

You gave me exactly the help I needed and it works
perfectly!!!!!!!!!!!!!!!!!!!!!

Thank You Verrrrry Much!
Rob

 

[Dave Peterson]

Actually, it's only pretty close.

Excel can see a number in the last 5 characters of: ASA001E1

001E1
is in scientific notation for 1*(10^1) (= 10)

But it does catch lots of things.