how about:

a = "11X1111111": if len(a) = 10 and mid(a,3,1) like "[A-z]" and
isnumeric(left(a,2)& right(a,7)) Then Cells(2,1) = "valid order" else
Cells(2,1) = "not valid order"

or

a = "11!1111111": Cells(2,1) = iif( len(a) = 10 and mid(a,3,1) like "[A-z]"
and isnumeric(left(a,2)& right(a,7)),"valid order" ,"not valid order")

?

;^)


"antinoz" wrote in message
...
I'm trying to validate data in a column of cells to check that the format
is
"NNANNNNNN" (N= number A= Alpha I have) all the cell contents are
different
but have 9 characters containing the alphanumeric format as shown

I want to put it in an If statement:

so if cell A1 = mask "NNANNNNNNN" cell A2 = "Valid order"

uh..., len(a) = 9..., of course.


"Bob Kilmer" wrote in message
...
how about:

a = "11X1111111": if len(a) = 10 and mid(a,3,1) like "[A-z]" and
isnumeric(left(a,2)& right(a,7)) Then Cells(2,1) = "valid order" else
Cells(2,1) = "not valid order"

or

a = "11!1111111": Cells(2,1) = iif( len(a) = 10 and mid(a,3,1) like
"[A-z]"
and isnumeric(left(a,2)& right(a,7)),"valid order" ,"not valid order")

?

;^)


"antinoz" wrote in message
...
I'm trying to validate data in a column of cells to check that the
format
is
"NNANNNNNN" (N= number A= Alpha I have) all the cell contents are
different
but have 9 characters containing the alphanumeric format as shown

I want to put it in an If statement:

so if cell A1 = mask "NNANNNNNNN" cell A2 = "Valid order"

Cheers Guy's plenty of food for thought there, I have absoultly no idea what
some of those formula's are doing but give me time to dissect it all and I
may get it!!

Thanks for all your responses cheers ant

PS sorry it was supposed to be 10 characters my fault for only putting 9
first time round
ie 12A1234567 oops!!


uh..., len(a) = 9..., of course.


"Bob Kilmer" wrote in message
...
how about:

a = "11X1111111": if len(a) = 10 and mid(a,3,1) like "[A-z]" and
isnumeric(left(a,2)& right(a,7)) Then Cells(2,1) = "valid order" else
Cells(2,1) = "not valid order"

or

a = "11!1111111": Cells(2,1) = iif( len(a) = 10 and mid(a,3,1) like
"[A-z]"
and isnumeric(left(a,2)& right(a,7)),"valid order" ,"not valid order")

?

;^)


"antinoz" wrote in message
...
I'm trying to validate data in a column of cells to check that the
format
is
"NNANNNNNN" (N= number A= Alpha I have) all the cell contents are
different
but have 9 characters containing the alphanumeric format as shown

I want to put it in an If statement:

so if cell A1 = mask "NNANNNNNNN" cell A2 = "Valid order"