Cell Format Check

[Rich]

I need a macro to check each cell in a range to see if it fits a
certain format. Specifically the format is

LLNNNNNNL

L = letter
N = number

Is there any easy way to do this?

Thanks,
Richard

[Tom Ogilvy]

s = "[A-Z][A-Z]#####[A-Z]"
for each cell in selection
if ucase(cell.Value) like s then


End if
Next

Demo'd from the immediate window:

s = "[A-Z][A-Z]#####[A-Z]"
? "AA12345B" like s
True
? "A012345B" like s
False
? "AA123456" like s
False
? "AA1234B" like s
False

--
Regards,
Tom Ogilvy


"Rich" wrote:

> I need a macro to check each cell in a range to see if it fits a
> certain format. Specifically the format is
>
> LLNNNNNNL
>
> L = letter
> N = number
>
> Is there any easy way to do this?
>
> Thanks,
> Richard
>
>

[Tom Ogilvy]

Miscounted the N's

Change it to

s = "[A-Z][A-Z]######[A-Z]"

with 6 #'s

concept is the same.

Just for completeness, if you don't want to include the Ucase, you can be
case insensitive with

s = "[A-Za-z][A-Za-z]######[A-Za-z]"

or to be case sensitive, use the original and don't use the ucase.

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote:

>
> s = "[A-Z][A-Z]#####[A-Z]"
> for each cell in selection
> if ucase(cell.Value) like s then
>
>
> End if
> Next
>
> Demo'd from the immediate window:
>
> s = "[A-Z][A-Z]#####[A-Z]"
> ? "AA12345B" like s
> True
> ? "A012345B" like s
> False
> ? "AA123456" like s
> False
> ? "AA1234B" like s
> False
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Rich" wrote:
>
> > I need a macro to check each cell in a range to see if it fits a
> > certain format. Specifically the format is
> >
> > LLNNNNNNL
> >
> > L = letter
> > N = number
> >
> > Is there any easy way to do this?
> >
> > Thanks,
> > Richard
> >
> >

[Rich]

Excellent

thanks very much