That formula fails when the number part has a decimal point or an E (or e)
in it. For example, try these values...

AB123.5

AB123e5

--
Rick (MVP - Excel)


"Ashish Mathur" wrote in message
...
Hi,

Try this formula. While in cell B85, enter this in Data Validation
Custom.

=AND(LEN(B85)=7,CODE(LEFT(B85,1))=65,CODE(LEFT(B8 5,1))<=90,CODE(MID(B85,2,1))=65,CODE(MID(B85,2,1) )<=90,ISNUMBER(1*RIGHT(B85,5)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

wrote in message
...
I want to use a non vb formula in data validation for an excel formula
to make the user enter the first 2 characters of a field as UPPERCASE
letters i.e. AB12345 - the format is always the same 2 letters 5
numbers.

Any ideas?