Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula anomaly
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Top N Pivot Anomaly | Excel Discussion (Misc queries) | |||
Combo Box Anomaly | Excel Discussion (Misc queries) | |||
Date anomaly | Excel Discussion (Misc queries) | |||
Sort anomaly | Excel Worksheet Functions | |||
VLOOKUP Anomaly | Excel Worksheet Functions |