How about:

=IF(AND(LEN(A1)=9,ISNUMBER(-REPLACE(A1,3,1,"")),CODE(UPPER(MID(A1,3,1)))64,
CODE(UPPER(MID(A1,3,1)<96))),"Valid Order","not Valid")

(all one cell)

antinoz wrote:

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"

--

Dave Peterson

I like this better:

=IF(LEN(A1)<9,"not Valid",
IF(NOT(ISNUMBER(-REPLACE(A1,3,1,""))),"not Valid",
IF(AND(TEXT(--REPLACE(A1,3,1,""),"00000000")=REPLACE(A1,3,1,""),
CODE(UPPER(MID(A1,3,1)))64,CODE(UPPER(MID(A1,3,1) <96))),
"Valid Order","not Valid")))

It removes any error if the length was too short and checks for entries like:
12A1234E7
(E can make it look like the entry was using scientific notation)


Dave Peterson wrote:

How about:

=IF(AND(LEN(A1)=9,ISNUMBER(-REPLACE(A1,3,1,"")),CODE(UPPER(MID(A1,3,1)))64,
CODE(UPPER(MID(A1,3,1)<96))),"Valid Order","not Valid")

(all one cell)

antinoz wrote:

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"

--

Dave Peterson


--

Dave Peterson