can i have a formula that looks for one thing but with exceptions?
i.e. =IF(LEN(A5)>3,1,"") but if A5 contains CMT, DIVS or FUND
many thanks
can i have a formula that looks for one thing but with exceptions?
i.e. =IF(LEN(A5)>3,1,"") but if A5 contains CMT, DIVS or FUND
many thanks
=IF(OR(ISERROR(SEARCH("CMT",A5)),ISERROR(SEARCH("DIVS",A5)),ISERROR(SEARCH("FUND",A5)),
IF(LEN(A5)>3,1,""), "" )
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Micayla Bergen" <[email protected]> wrote in message
news:[email protected]...
> can i have a formula that looks for one thing but with exceptions?
> i.e. =IF(LEN(A5)>3,1,"") but if A5 contains CMT, DIVS or FUND
> many thanks
Thanks Bernard. however i get an error saying there is a missing parenthesis,
so i have added one to the end, then i get a formula message saying i am
missing the value if true value if false values
"Bernard Liengme" wrote:
> =IF(OR(ISERROR(SEARCH("CMT",A5)),ISERROR(SEARCH("DIVS",A5)),ISERROR(SEARCH("FUND",A5)),
> IF(LEN(A5)>3,1,""), "" )
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Micayla Bergen" <[email protected]> wrote in message
> news:[email protected]...
> > can i have a formula that looks for one thing but with exceptions?
> > i.e. =IF(LEN(A5)>3,1,"") but if A5 contains CMT, DIVS or FUND
> > many thanks
>
>
>
so i have tried this
=IF(OR(ISERROR(SEARCH("CMT",A6)),ISERROR(SEARCH("DIVS",A6)),ISERROR(SEARCH("FUND",A6))),,IF(LEN(A6)>3,1,""))
and still get nothing
"Bernard Liengme" wrote:
> =IF(OR(ISERROR(SEARCH("CMT",A5)),ISERROR(SEARCH("DIVS",A5)),ISERROR(SEARCH("FUND",A5)),
> IF(LEN(A5)>3,1,""), "" )
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Micayla Bergen" <[email protected]> wrote in message
> news:[email protected]...
> > can i have a formula that looks for one thing but with exceptions?
> > i.e. =IF(LEN(A5)>3,1,"") but if A5 contains CMT, DIVS or FUND
> > many thanks
>
>
>
try
=IF(OR(ISERROR(SEARCH("CMT",A5)),ISERROR(SEARCH("DIVS",A5)),ISERROR(SEARCH("FUND",A5))),IF(LEN(A5)>3,1,""), "" )
but I'm not sure if the logic is what you want - since I don't know what you want
not a professional, just trying to assist.....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks