|
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 |
Formula anomaly
The issue is in the last section
IF(AND(CODE(RIGHT(A1,1))=97,CODE(RIGHT(A1,1))<=12 2),TRUE,FALSE) both of the AND conditions are true for 000000d. Add a condition to exclude 000000x IF(AND(CODE(RIGHT(A1,1))=97,CODE(RIGHT(A1,1))<=12 2,LEFT(A1,6)<"000000"),TRUE,FALSE), the full formula would be =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,LEFT(A1,6)<"00 0000"),TRUE,FALSE),FALSE)))) -- If this helps, please remember to click yes. "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 |
Formula anomaly
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 |
Formula anomaly
Paul,
That did the trick. Thanks! Bob "Paul C" wrote: The issue is in the last section IF(AND(CODE(RIGHT(A1,1))=97,CODE(RIGHT(A1,1))<=12 2),TRUE,FALSE) both of the AND conditions are true for 000000d. Add a condition to exclude 000000x IF(AND(CODE(RIGHT(A1,1))=97,CODE(RIGHT(A1,1))<=12 2,LEFT(A1,6)<"000000"),TRUE,FALSE), the full formula would be =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,LEFT(A1,6)<"00 0000"),TRUE,FALSE),FALSE)))) -- If this helps, please remember to click yes. "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 |
Formula anomaly
Luke,
Thanks for your help. I really appreciate it. I like your very concise formula, and it fixes the anomaly to boot! The only remaining problem is your formula yields a #VALUE! error if cell A1 is blank. Can you tell me how to modify your formula to account for the cell being blank (i.e., blank cell = FALSE)? Regards, Bob "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 |
Formula anomaly
Hi,
Here is a somewhat shorter version, 35 characters shorter, which seems to do the trick: =IF(OR(LEFT(A1,6)="000000",AND(--LEFT(A1,6)99999,--LEFT(A1,6)<10^6)),IF(LEN(A1)=6,TRUE,IF(AND(LEN(A1) =7,--CODE(MID(A1,7,1))96,--CODE(MID(A1,7,1))<122),TRUE,FALSE))) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "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 |
Formula anomaly
Hi Shane,
Your shorter version is very impressive, and I really appreciate your help. Unfortunately, input values such as "000000a" and "dd" in cell A1 result in your formula evaluting to #VALUE! rather than the correct result of FALSE. Also, I have never seen "--" used in front of the LEFT and CODE functions. What does "--" cause these functions to do? Thanks, Bob "Shane Devenshire" wrote: Hi, Here is a somewhat shorter version, 35 characters shorter, which seems to do the trick: =IF(OR(LEFT(A1,6)="000000",AND(--LEFT(A1,6)99999,--LEFT(A1,6)<10^6)),IF(LEN(A1)=6,TRUE,IF(AND(LEN(A1) =7,--CODE(MID(A1,7,1))96,--CODE(MID(A1,7,1))<122),TRUE,FALSE))) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "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 |
Formula anomaly
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 |
| All times are GMT +1. The time now is 08:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com