Hi Dana,

This will differentiate upper and lower case:

=AND(OR(ASC(LEFT(A1,1))=97,ASC(LEFT(A1,1))=99,ASC(LEFT(A1,1))=101,ASC(LEFT(A1,1))=102), LEN(A1)=7, ISNUMBER(VALUE(MID(A1,2,6))))

Regards,
Stefi

„Dana” ezt *rta:

> Stefi,
>
> Thanks for the prompt reply.
>
> In Validation I changed the Allow: box to Custom. Entered the formula in
> the Formula: box changing the 'A1' to 'A2' to allow for my titles.
>
> It worked to require 7 digits and to require the a, c, e, or f. But it
> allowed both upper case and lower case.
>
> Any further suggestions? In case it matters, I'm using Excel 2003 SP1.
>
> Regards,
>
> Dana
>
>
> "Dana" wrote:
>
> > I’m creating a spreadsheet for data collection from various employees and am
> > trying to make it as “goof-proof” as possible.
> >
> > The first column must not be blank, must be unique, and requires one (1)
> > lower case text character (currently a, c, e, or f) followed by any six (6)
> > numbers. i.e. e123456. This will be used to validate against a master
> > database later.
> >
> > I have a conditional format set to show if the cell is duplicated using
> > =COUNTIF($A$2:$A$1000,A2)>1. The format for this condition changes the color
> > of both duplicate cells to red so they can correct it.
> >
> > I have validation set to require exactly seven (7) characters. The “ignore
> > blank” checkbox is unchecked. The input and stop messages gives them an
> > example.
> >
> > This still allows any entry that is seven characters, numeric or text (upper
> > or lower case) or any combination.
> >
> > What I’d like to do is test their entry for our exact requirements and if
> > possible, stop them from moving on until it is correct. Can this be done
> > using worksheet functions or does it require a VBA script?
> >
> > I reallize this would probably be easier in MS Access, but not all have the
> > program and many wouldn't be able to use it.
> >
> > TIA.
> >
> > Dana Scott
> > dascott@laisd.org
> >