+ Reply to Thread
Results 1 to 6 of 6

Search formula = If cell contains word located in another cell = true

  1. #1
    Forum Contributor
    Join Date
    01-06-2012
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    165

    Search formula = If cell contains word located in another cell = true

    Hi,

    I've been trying to write a formula to return a yes or no if part of a cell contains a value stored in a separate cell (B1 in this case).


    Ive tried =IF(ISNUMBER(SEARCH(d1,A2)),"Yes","No")

    But obviously that will probably look for an entire match. Whereas I have data like this
    A B
    2014/09/01
    2014/09/02 testing false
    2014/09/01 testing true

    Where data in column A has other data included. Any help you guys can offer would be v. much appreciated
    Last edited by BPSJACK; 09-05-2014 at 11:48 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: Search formula = If cell contains word located in another cell = true

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Your formula checks for part of the cell as well.
    ________________________________________________________
    If your problem is solved, update the thread as SOLVED: Go to the top of the first post-Select Thread Tools-Select Mark thread as Solved OR - Go to the first post - Click edit- Click Advance- Just below the word "Title:" you will see a dropdown with the word No prefix.- Change to Solve- Click Save.

    Show your gratitude to the person who helped you solving your problem by clicking on star button at the bottom of such post.

  3. #3
    Forum Contributor
    Join Date
    01-06-2012
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    165

    Re: Search formula = If cell contains word located in another cell = true

    That is fantastic you have saved me so much work! Thank you

  4. #4
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Search formula = If cell contains word located in another cell = true

    You are searching a text string for a value Excel is recognizing as a date (and therefore a number).

    Try this:

    Please Login or Register  to view this content.
    Cheers,
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  5. #5
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Search formula = If cell contains word located in another cell = true

    So in my testing, the proposed IF using SEARCH worked perfectly.

    ....Of course, I had to force the reference value to be input as a text string, rather than be automatically converted to a serial number (41883 for 1st Sept. 2014), so that might cause you trouble...? Well, without knowing more about your data structure, that might or might not be a problem.

  6. #6
    Forum Contributor
    Join Date
    01-06-2012
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    165

    Re: Search formula = If cell contains word located in another cell = true

    Thanks for the advice, I've updated the formula accordingly and it works like a charm.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula to search for one word in adjacent cell
    By ZeDoctor in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-08-2014, 06:12 AM
  2. Replies: 1
    Last Post: 01-25-2011, 10:50 PM
  3. Replies: 2
    Last Post: 09-14-2009, 05:15 AM
  4. Search a range for value and if true put a x in a cell
    By AdRock952 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2008, 07:43 PM
  5. [SOLVED] Search in excel by date located in a cell
    By Btobin0 in forum Excel General
    Replies: 6
    Last Post: 05-27-2006, 10:25 AM

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