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

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

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

Excellent

thanks very much