Search Cell Range For Words In List

  • I am trying to find a way to search a cell or a combination of cells, for key words that are in a separate list. The separate list is dynamic, meaning it will be changed, expanded, or contracted at various times.


    I would also like the search to return a specified numeric value to a different cell if it finds one of these words. If it does not find one of the words I would like it to return a separate specified value.


    I attached an example to help make things more clear.

  • Re: Search A Cell For Mutilple Text Values


    Welcome to Oz.


    Please workbooks, not pictures, if you want help with Excel.


    [COLOR="Blue"]=IF( OR( ISNUMBER( SEARCH(wordList, A1) ) ), -1, 0)[/COLOR]


    This is an array formula, meaning it MUST be confirmed with Ctrl+Shift+Enter, not just Enter. (With the cursor in the formula bar, hold down Ctrl and Shift, then press Enter.) You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.


    Change SEARCH to FIND if you want a case-sensitive match.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Search A Cell For Mutilple Text Values


    This assumes keywords in F1 and down, values in A1 and down. Come back if it doesn't do what you want.


    Ho hum...much simpler shg!

  • Re: Search A Cell For Mutilple Text Values


    Thanks you for your replies. Both methods work perfect but incase some else looks at this thread later there are a couple things I noticed:
    VB Method
    • Really easy
    • Case sensitive
    • can only do about 100 entires, otherwise it takes a while to run
    Method with Built in excel functions
    • Extremely fast (did 30000 entries in a matter of seconds)
    • Can adjust case sensitivity
    • MUST push F4 after wordlist
    • can have search two or more consecutive words


    Once again thanks for your time and effort

  • Re: Search A Cell For Multiple Text Values


    Quote

    VB Method
    ...
    • Case sensitive


    Could easily be made case insensitive

    Quote

    Method with Built in excel functions
    ...
    • MUST push F4 after wordlist


    Not if you use a named range.

    Quote from StephenR

    Ho hum...much simpler shg!


    Yes, but if you needed to do this in VBA, I don't think you can do it without a loop as you did, even though Search and Find are available in WorksheetFunction. I just looked at that a day or two ago. If you figure out how, let me know!

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Search A Cell For Multiple Text Values


    try

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!