Hi,

Agreed. Thank you for the clarification.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Rick Rothstein" wrote in message
...
The OP was after a Data/Validation formula that would guarantee a user's
input was of the form letter-letter-number-number-number-number-number...
that is the desired entry format... that doesn't mean the user will
*always* type in such an entry... for example, the user could type in
AB123e5 by mistake (accidentally hitting the 'e' when he/she went for the
'4' instead) or he/she could type in AB123.4 just to be mischievous... I
would think the Data/Validation formula should be able to handle such
occurrences.

--
Rick (MVP - Excel)


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

Thank you for your comments. In the original question, it has been
mentioned that the last 5 characters are numbers - therefore the
possibility of having a . or e is ruled out.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Rick Rothstein" wrote in message
...
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?