ISNUMBER(--RIGHT(A1,3))

That will allow entries like:

A.00
A1e1
A10.
A1.1

--
Biff
Microsoft Excel MVP


"Chip Pearson" wrote in message
...

In the Validation dialog, choose Custom from the Allow list and use
the following formula:

=AND(LEN(A1)=4,LEFT(A1,1)="A",LEFT(A1,1)<="Z",ISN UMBER(--RIGHT(A1,3)))

This will allow the first character to be either upper or lower case.
If you want to force upper case, use

=AND(EXACT(LEFT(A1,1),UPPER(LEFT(A1,1))),
LEN(A1)=4,LEFT(A1,1)="A",LEFT(A1,1)<="Z",ISNUMBER (--RIGHT(A1,3)))

If you want to force lower case, use

=AND(EXACT(LEFT(A1,1),LOWER(LEFT(A1,1))),
LEN(A1)=4,LEFT(A1,1)="A",LEFT(A1,1)<="Z",ISNUMBER (--RIGHT(A1,3)))

Of course, change all occurrences of A1 to your actual cell address.


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]






On Wed, 13 Jan 2010 09:48:01 -0800, yclhk
wrote:

How to set a cell requested to input in the format of A123 (i.e. one
letter
plus three digits) by data validation .