+ Reply to Thread
Results 1 to 8 of 8

[SOLVED] Searching A Cell for multiple text strings (a whole list) with one formula?

  1. #1
    Registered User
    Join Date
    12-13-2015
    Location
    Atlanta,GA
    MS-Off Ver
    Office 2013
    Posts
    3

    Lightbulb [SOLVED] Searching A Cell for multiple text strings (a whole list) with one formula?

    Hello, I am new to the forum and I've searched around, but I didn't see a solution to my problem that specifically works in the way I am looking for it to work, so I figured I'd ask here and see if there is a solution. I've been wrecking my brain for the best solution for this. Here Goes:

    I have one sheet (below) where I enter Branded Terms (think Apple, Ipod, iTunes etc....)

    Branded Terms cells.jpg


    Then I have another sheet (below) where I import a list of SEARCH TERMS, you'll see those in the leftmost column. These can number into the thousands of search terms.

    Search Terms cells sheet.jpg

    I am trying to find the Best Solution to Search each cell of SEARCH TERMS for the Branded Terms I entered in the list on the other sheet. Once the formula identifies that none of the Branded terms are in the cell then it should move the cell contents into the gray area next to it. Obviously I know how to use the Search and Find functions, but I haven't found a way to used them for mulitple words in the list i.e. ( search(OR x,y,z,m,n,o,p)) A Search OR doesn't seem to work for an entire list. As you can see the list of terms that I enter in the first sheed MAY be 5 terms OR 100 terms and I need that formula to be dynamic in that it searches the cell for ALL of the terms in the list (that's why I made it into a table, but maybe this isn't the way to go?) I know I can string together a long If formula with Static Search values but that wouldn't work at all. I know I've tried a few things like Sumproduct array formulas but there seems to be a rub there too. It only returns an error if ALL of the words aren't/are found. I need the formula to search for ALL of the terms in the initial list and if even ONE of them are found for it to return an error.

    Please let me know if I'm not being clear.

    Thanks for any help you can provide, I've been working on this for weeks and I know how to do ham-handed workthroughs, but they still take all day and are only reliable as your eyes. I'm trying to write this formula so that I can strip out ALL NON-Branded search terms into one list.

    **One other thing! Of course the initial list is text that I enter and will change whenever I have a new Brand I am trying to scrape search terms for, so I can't do static values in the formula i.e. (SumProduct(List,"apple","ipod","ipad"). The formula is going to have to reference cells or Tables etc so that the contents of those things can be changed at will.


    Thanks,

    IvanW
    Last edited by IvanW; 12-14-2015 at 01:21 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,519

    Re: Searching A Cell for multiple text strings (a whole list) with one formula?

    Please post small but representative file so we have some data to work with.
    Last edited by JohnTopley; 12-13-2015 at 12:12 PM.

  3. #3
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,089

    Re: Searching A Cell for multiple text strings (a whole list) with one formula?

    Hi
    Try this code VBA in a Module
    Please Login or Register  to view this content.
    Use this formula in the gray area
    =SUBST_EVERY(A2,$B$2:$B$55)
    and copy down

    $B$2:$B$55 is your "Table for Branded Terms"

    See if this help

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,519

    Re: Searching A Cell for multiple text strings (a whole list) with one formula?

    Another VBA solution; a subroutine using Jose's substitute formula.



    Please Login or Register  to view this content.

    the Branded Names are in a named range called "Table" (column "A" on Sheet2)
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-13-2015
    Location
    Atlanta,GA
    MS-Off Ver
    Office 2013
    Posts
    3

    Re: Searching A Cell for multiple text strings (a whole list) with one formula?

    John Topley, Just tried your excel file and it's AWESOME except it DOES move the contents of the cell that aren't in the Table, how would I modify the VBA for it to not move that cell AT ALL if it finds one of the terms from the table?

    i.e

    Table
    Apple
    Ipod
    Ipad
    Apple music

    Search Terms Search Terms That are selected below
    Great Music players Great Music players
    Best Ipod
    apple ipod
    awesome mp3 player awesome mp3 player
    apple products
    apple ipad


    As you can see the only terms that were selected to be moved were terms that didn't include ANY Branded Terms from the Table, but also I don't want any of the contents of the cells that DO contain Branded Terms.


    Thank you Jose as well I'm going to try your code on its own while I await a modification from John.

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,003

    Re: Searching A Cell for multiple text strings (a whole list) with one formula?

    Using John Topley's sample worksheet ,

    Another formula approach:

    With initial list in A2:A19

    With Branded Term in Sheet2!$A$2:$A$7

    Try in B2:

    =IF(OR(ISNUMBER(SEARCH(Sheet2!$A$2:$A$7,A2))),"",A2)

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Drag down.
    Attached Files Attached Files

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,003

    Re: Searching A Cell for multiple text strings (a whole list) with one formula?

    Duplicate post!
    Last edited by bebo021999; 12-14-2015 at 12:58 AM.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,519

    Re: Searching A Cell for multiple text strings (a whole list) with one formula?

    See attached
    Attached Files Attached Files
    Last edited by JohnTopley; 12-14-2015 at 06:26 AM.

  9. #9
    Registered User
    Join Date
    12-13-2015
    Location
    Atlanta,GA
    MS-Off Ver
    Office 2013
    Posts
    3

    Re: Searching A Cell for multiple text strings (a whole list) with one formula?

    Thanks to everyone for their answers. Special Thanks to John Topley, This VBA solution is exactly what I was searching for. THIS IS SOLVED!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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