+ Reply to Thread
Results 1 to 9 of 9

How to limit multi-value (OR) FIND or SEARCH to the first or last word in a string?

  1. #1
    Registered User
    Join Date
    02-04-2012
    Location
    CA, USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    How to limit multi-value (OR) FIND or SEARCH to the first or last word in a string?

    Hi all,

    Can a FIND or SEARCH formula be limited to detect multiple 'OR' values only if the values are the first or last in a string? (I don't need a single formula that checks for both conditions; I can use 2 formulas if it's significantly easier).

    For example, can =OR(ISNUMBER(SEARCH({"dog","mouse","elephant"},A1))) be modified to detect "Dog 123" or "Mouse123 ABC" or "123 XYZ Elephant" while ignoring cells containing any of the values in an intermediate position?

    Thanks for any help!
    Last edited by Buzzed Aldrin; 07-14-2012 at 01:45 PM. Reason: Wrong initial problem statement

  2. #2
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: How to limit FIND or SEARCH to either the first or last word in a string?

    =if(or(left(a1,3)="abc",right(a1,3)="abc"),"replacewithtruevalue","replacewithfalsevalue")
    If your question has been satisfactorily addressed, please consider marking it solved. Click the Thread Tools dropdown and select Mark thread as solved.
    Also, you might want to add to the user's reputation by clicking the star icon in the lower left corner of the post with the answer- it's why we do what we do...

    Thomas Lafferty
    Analyst/Programmer

  3. #3
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: How to limit FIND or SEARCH to either the first or last word in a string?

    BTW - I understand you were looking for find or search, but it's not necessary. Simple is usually better.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,742

    Re: How to limit FIND or SEARCH to either the first or last word in a string?

    Maybe (If all you want is True/False):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Replace WORD with the cell address of the lookup val
    Last edited by protonLeah; 07-13-2012 at 08:24 PM.
    Ben Van Johnson

  5. #5
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Re: How to limit FIND or SEARCH to either the first or last word in a string?

    Just to show some alternatives:
    =OR(LEFT(A1;LEN("ABC"))="ABC";RIGHT(A1;LEN("ABC"))="ABC")
    =AND(SEARCH("ABC";A1);OR(ISERROR(SEARCH("?"&"ABC";A1));ISERROR(SEARCH("ABC"&"?";A1))))
    //Ola

  6. #6
    Registered User
    Join Date
    02-04-2012
    Location
    CA, USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: How to limit FIND or SEARCH to either the first or last word in a string?

    Sorry folks, I really screwed up my initial problem statement by neglecting some 'minor' details, namely that the search involves multiple 'OR' values where each one can consist of a different number of characters.

    I've revised the question, hopefully clear this time.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,742

    Re: How to limit multi-value (OR) FIND or SEARCH to the first or last word in a string?

    I think you'd be better off using helper columns and smaller formulas.

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How to limit multi-value (OR) FIND or SEARCH to the first or last word in a string?

    If you have a long list of possible search strings then it would be easier with those in a table.

    Without a table

    =OR(COUNTIF(A1,{"dog*","mouse*","elephant*","*dog","*mouse","*elephant"}))

    With a table, i.e. listing dog, mouse, elephant in E1:E3

    =OR(COUNTIF(A1,CHOOSE({1,2},"*"&$E$1:$E$3,$E$1:$E$3&"*")))

    The second formula must be array confirmed with Shift Ctrl Enter.

  9. #9
    Registered User
    Join Date
    02-04-2012
    Location
    CA, USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: How to limit multi-value (OR) FIND or SEARCH to the first or last word in a string?

    Quote Originally Posted by jason.b75 View Post
    If you have a long list of possible search strings then it would be easier with those in a table.

    Without a table

    =OR(COUNTIF(A1,{"dog*","mouse*","elephant*","*dog","*mouse","*elephant"}))

    With a table, i.e. listing dog, mouse, elephant in E1:E3

    =OR(COUNTIF(A1,CHOOSE({1,2},"*"&$E$1:$E$3,$E$1:$E$3&"*")))

    The second formula must be array confirmed with Shift Ctrl Enter.
    I can't count how many times I've overlooked the simplicity/power of wildcards. Both versions appear to work for all cases, xlnt, and yes I do have many search values so the table version is great, THANKS VERY MUCH!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1