formula

[Guest]

=IF(OR(ISERROR(SEARCH("CMT",A4)),ISERROR(SEARCH("DIVS",A4)),ISERROR(SEARCH("FUND",A4))),"",
IF(LEN(A4)>3,1,""))

i have this formula which should return a value of 1 if A5 contains more
than 3 letters except if A5 contains CMT, DIVS or FUND
what am i missing?
many thanks

 

[Guest]

Well, I'm assuming you mean "A4", not "A5", in your statement. If A4 is the
target cell, try:

=IF(OR(LEN(A4)<=3,SUM(COUNTIF(A4,{"*cmt*","*divs*","*fund*"}))>0),"",1)

HTH
Jason
Atlanta, GA

 

[Max]

Perhaps try also:

=IF(ISNUMBER(MATCH(A4,{"CMT";"DIVS";"FUND"},0)),"",IF(LEN(A4)>3,1,""))

 

[CLR]

Really cool Jason, but if the OP really meant "3 letters" instead of "3
characters", then this maybe........

=IF(ISNUMBER(A4),"",IF(OR(LEN(A4)<=3,SUM(COUNTIF(A4,{"*cmt*","*divs*","*fund
*"}))>0),"",1))

Vaya con Dios,
Chuck, CABGx3

 

[Max]

If A4 were to contain for example: CMTS or FUNDS or DIVS#
I'm not sure whether the OP would want a "1" to be returned ..