I figured it out:

=IF(NOT(ISBLANK(A1)),IF(OR(A1="000000",AND(LEN(A1) =6,IF(ISERROR(VALUE(A1)),0,A1)=100000),AND(IF(ISE RROR(VALUE(LEFT(A1,6))),0,VALUE(LEFT(A1,6)))=1000 00,LEN(A1)=7,CODE(RIGHT(A1,1))=97,CODE(RIGHT(A1,1 ))<=122)),TRUE,FALSE),FALSE)


"Luke M" wrote:

Here is a corrected formula:

=IF(OR(A1="000000",
AND(LEN(A1)=6,IF(ISERROR(VALUE(A1)),0,A1)=100000) ,
AND(IF(ISERROR(VALUE(LEFT(A1,6))),0,VALUE(LEFT(A1, 6)))=100000,LEN(A1)=7,CODE(RIGHT(A1,1))=97,CODE( RIGHT(A1,1))<=122)),
TRUE,FALSE)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Bob" wrote:

Cell A1 is formatted as TEXT, and IF it contains either:

000000 (six zeros)
OR
the number 100000 through and including 999999
OR
the number 100000 through and including 999999 with a lower-case letter
appended (e.g., 274651b, 822937g, etc.)

then evaluate to TRUE. Otherwise, evaluate to FALSE.

I have written the following formula in cell B1:

=IF(ISBLANK(A1)=TRUE,FALSE,IF(A1="000000",TRUE,IF( AND(LEN(TEXT(A1,"#"))=6,CODE(RIGHT(A1,1))=48,CODE (RIGHT(A1,1))<=57),IF(VALUE(LEFT(A1,6))=100000,TR UE,FALSE),IF(LEN(TEXT(A1,"#"))=7,IF(AND(CODE(RIGHT (A1,1))=97,CODE(RIGHT(A1,1))<=122),TRUE,FALSE),FA LSE))))

Everything appears to work correctly, except when A1 = 000000 + a lower-case
letter (e.g., 000000d). Instead of evaluating to FALSE, my formula evaluates
to TRUE.

Can anyone tell me how to modify my formula to fix this one anomaly?

Any help would be greatly appreciated.

Thanks,
Bob