• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Substring search exclusions

will.smith3

will.smith3

New Member
This one is similar to my last posting on searching strings, but is a bit different.

How can I search for a substring, but add a clause of some kind that contains exclusions?

I want to search for "change", but exclude the word "exchange"... All help is greatly appreciated.

here is my giant nested-if formula.

Code: 
=IF(COUNT(SEARCH("exchange",$G143&$T143)),"",IF(COUNT(SEARCH({"new","creat*"},$G143&$T143)),Categories!$A$2,IF(COUNT(SEARCH({"edit","modify","alias","update","*access","change","send?as","hide","GAL","SMTP"},$G143&$T143)),Categories!$A$3,IF(COUNT(SEARCH({"block*","leave","absence*"},$G143&$T143)),Categories!$A$4,IF(COUNT(SEARCH({"disable","remove","delet*","close","deactivat*","terminat*"},$G143&$T143)),Categories!$A$5,IF(COUNT(SEARCH("share?",$G143&$T143)),Categories!$A$6,IF(COUNT(SEARCH("outlook",$G143&$T143)),Categories!$A$7,IF(COUNT(SEARCH({"rout*","can*send"},$G143&$T143)),Categories!$A$8,IF(COUNT(SEARCH("external",$G143&$T143)),Categories!$A$9,IF(COUNT(SEARCH({"NDR","bounce","undeliver*"},$G143&$T143)),Categories!$A$10,IF(COUNT(SEARCH("transfer*",$G143&$T143)),Categories!$A$11,IF(COUNT(SEARCH({"export","archive","pst"},$G143&$T143)),Categories!$A$12,IF(COUNT(SEARCH("r*directory",$G143&$T143)),Categories!$A$14,IF($B143="eDiscovery",Categories!$A$16,IF(COUNT(SEARCH("fax*",$G143&$T143)),Categories!$A$17,IF(COUNT(SEARCH({"password?","reset","password reset"},$G143&$T143)),Categories!$A$18,IF(COUNT(SEARCH({"spam","phishing","compromise"},$G143&$T143)),Categories!$A$19,IF(COUNT(SEARCH({"calendar*","equipment","room"},$G143&$T143)),Categories!$A$20,IF(COUNT(SEARCH({"OWA","web*mail"},$G143&$T143)),Categories!$A$22,IF(COUNT(SEARCH({"EOP","zix"},$G143&$T143)),Categories!$A$23,IF(COUNT(SEARCH("scan",$G143&$T143)),Categories!$A$24,IF(COUNT(SEARCH({"out of office","automatic","repl*"},$G143&$T143)),Categories!$A$26,""))))))))))))))))))))))
 
Would it work to search and replace any occurrences of "exchange" first and then search for the substring "change"?

=FIND("change",SUBSTITUTE(B2,"exchange",""))
 
Will,

I'm impressed (and a little bit motion sick ;)) reading your Nested If: 1558 characters. -- woah. It does the job, I guess?

It seems like it would be somewhat cumbersome to work with...I hope you won't mind my suggesting an alternative?

Since you already reference a "Categories" tab in your formula, I imagined what that tab might look like, and then I built a formula with the same "functionality" of your if statement -- but with some additional flexibility (and brevity).

=IF(COUNT(SEARCH("exchange",$G143&$T143)),"",INDEX(Categories!$A$1:$A$26,MIN(IF(IFERROR(SEARCH(IF(Categories!$B$2:$K$26=0,REPT("x",10),Categories!$B$2:$K$26),$G143&$T143),0)>=1,ROW(Categories!$B$2:$K$26)))))

See attached. I built a dummy spreadsheet that works with your original formula works -- then I added my proposed formula for comparison. In this format, if you want to add criteria to your "nested if", you can just put the strings anywhere in the box on the Categories tab. The INDEX function in the formula will return the "results" from Column A in the Categories Tab.

Only one qualifier: the formula is an array formula, so it will require Ctrl+Shift+Enter to work.

All best with your 'nest'!
 

Attachments

  • Will1.xlsx
    11.9 KB · Views: 4
Last edited:
Another alternative is to use a lookup table with wildcards, and carefully order your list of lookup terms in order of preference. Here's a screenshot from a book I'm writing that has such an example:

lookup.gif
 
You must log in or register to reply here.
Back
Top