+ Reply to Thread
Results 1 to 4 of 4

Search for multiple text strings

  1. #1
    Forum Contributor
    Join Date
    01-25-2010
    Location
    Virginia
    MS-Off Ver
    Excel 2013
    Posts
    104

    Search for multiple text strings

    What is the best way to search for the presence of a list of text strings?

    Attached is a sample file.

    The "Data records" worksheet contains a data column called "Title" and another column called, "Filter check".

    The "Current filters list" worksheet contains in the "Filters List" column a list of text strings. This list is likely to grow over time.

    I'd like to know the best way to check each Title field in the Data records worksheet for the presence of any of the text strings in the Current filters list worksheet's Filters List.

    For example, the first Filters List value is "happy cow". Since *happy cow* is not contained in any of the Title values in the Data records worksheet, it should not get a value in the Filter check column. However, the 4th and 5th records in the Data records worksheet should get a "Yes" in the Filter check column, because they both do contain text strings that are included in the Current filters list worksheet's Filters List column.

    I've used SEARCH to search for single strings within strings, but I don't know the best way to handle this situation. My actual list of filters is about 15 - 20 different text strings.

    Can someone help me with this?

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Search for multiple text strings

    Using your sample:

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    01-25-2010
    Location
    Virginia
    MS-Off Ver
    Excel 2013
    Posts
    104

    Re: Search for multiple text strings

    Thank you VERY much. Wow.

    Can you help me understand your formula? What is it doing and what is the 9.99E+307?
    Last edited by DonkeyOte; 02-10-2011 at 09:49 AM. Reason: removed unnecessary quote

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Search for multiple text strings

    From a work flow perspective:

    Quote Originally Posted by DonkeyOte Logic
    - a SEARCH is conducted for each search phrase/word against the main string

    - SEARCH will return a Number (found) or #VALUE! (not found)

    - LOOKUP in this instance will find the last number listed in our lookup_vector (search results) and return the associated value from our result vector (state list)
    LOOKUP works per the above because:

    Quote Originally Posted by DonkeyOte Logic
    - our criteria value is a VERY big number (9.99*10^307)

    - LOOKUP assumes all values in the lookup_vector are ascending order (irrespective of reality)

    - LOOKUP ignores values in the lookup_vector not of the same data type as our criteria (including errors)

    - LOOKUP retrieves the last value <= criteria from lookup_vector (or associated result_vector result where specified)

+ 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