+ Reply to Thread
Results 1 to 6 of 6

Formula for seaching for several words in 1 cell

  1. #1
    Registered User
    Join Date
    02-29-2012
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2010
    Posts
    20

    Formula for seaching for several words in 1 cell

    Hi, Ive been helped with the following formula and wondering if anyone knows if I can add additional words for it to search for (still in the same cell).. Just a little tweak..

    eg if cell A1 contains "its a beautiful day" and we are searching for the word "beautiful" or "Day" or "Strawberries" then the contents of cell B1 will be displayed becuase one of the words is contained in A1.

    =IF(ISNUMBER(SEARCH("Beautiful", A1)), B1, "")

    If A1 contains one of the words then it will display the contents of B1.

    Thanks for helping it's so appreciated.

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Formula for seaching for several words in 1 cell

    Try:

    =IF(ISNA(MATCH(TRUE,INDEX(ISNUMBER(SEARCH({"Beautiful","Day"},A1)),0),0)),"",B1)

    Edited to add: Just to clarify, you can add more words to your search by including them, in quotes and comma separated, within the curly braces, e.g.:

    =IF(ISNA(MATCH(TRUE,INDEX(ISNUMBER(SEARCH({"Beautiful","Day","Fish","Banana"},A1)),0),0)),"",B1)
    Last edited by Andrew-R; 05-10-2012 at 06:42 AM. Reason: Can't spell "curly"

  3. #3
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Formula for seaching for several words in 1 cell

    Maybe
    =If(OR(ISNUMBER(SEARCH({"beautiful","day"},A1))),B1,"")

  4. #4
    Registered User
    Join Date
    02-29-2012
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Formula for seaching for several words in 1 cell

    Hi, thanks for your help in advance
    When cell A1 contains "its a beautiful day" I've been successfully using this formula =IF(ISNA(MATCH(TRUE,INDEX(ISNUMBER(SEARCH({"Beautiful","Day","Fish","Banana"},A1)),0),0)),"",B1) to return the value of B1

    Now I have been trying hard (unsuccessfully) to modify it and hoping for your help to make my day easier
    Instead of adding each word eg "Beautiful","Day","Fish","Banana" to the formula I would like each word to be placed in individual cells eg D1 would contain "Beautiful", D2 would contain "Day" etc.
    The new formula would reference these cells instead of typing in the new words each time and adjusting the formula. (sometimes there are more than 20 words). Some cells would not contain any words eg D3 could be blank

    Thank you all very much, have a great day

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Formula for seaching for several words in 1 cell

    It seems to work the way that you describe:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    D1 = beautiful; D2 = day; D3 is blank; D4 = fish; D5 = banana
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Formula for seaching for several words in 1 cell

    Another option:
    Please Login or Register  to view this content.
    Quang PT

+ 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