+ Reply to Thread
Results 1 to 4 of 4

search for multiple text strings?

  1. #1
    Registered User
    Join Date
    03-03-2008
    Posts
    2

    search for multiple text strings?

    Is there a simple way to search within a large text string to see if at least one short text string from a list is present? I do not want to have to test for each of the short strings individually inside an OR() function.

    For example, I want to search a fast food restaurant address in cell B1 to see if it contains any of the 10 string fragments: "Burger Kin", "acdonald's", ..., or "aco Bell" tabulated in cells C11:C20.

    I've tried using array formulas like {=SEARCH(C11:C20,B1)} and {=MATCH("*"&C11:C20&"*",B1)}, but they only work for the first item in the list. I've also tried incorporating OR() functions in the array formulas, but they do not work with variables (references to locations in the table, rather than the strings themselves).
    Last edited by pbgvdad; 03-03-2008 at 01:58 PM.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Maybe this array formula (Enter with Ctrl + Shift + enter)

    =IF(OR(NOT(ISERROR(SEARCH(C11:C20,B1)>0))),"Found","Not Found")
    Assumes No blanks in C11:C20

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    You can use this formula

    =IF(COUNT(SEARCH(C11:C20,B1)),"match","no match")

    which requires CTRL+SHIFT+ENTER

    Avoiding CSE...

    =IF(SUMPRODUCT(--ISNUMBER(SEARCH(C11:C20,B1))),"match","no match")

    Both of the above assume no blanks in C11:C20. If you want to allow blanks try

    =IF(SUMPRODUCT(--ISNUMBER(SEARCH(C11:C20,B1)),--(C11:C20<>"")),"match","no match")

  4. #4
    Registered User
    Join Date
    03-03-2008
    Posts
    2

    Thumbs up They work!

    Thank you both! All 4 formulas work nicely.

+ 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