+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : Return True or False if any word in a list is in a string

  1. #1
    Registered User
    Join Date
    10-05-2011
    Location
    Adelaide
    MS-Off Ver
    Excel 2003
    Posts
    4

    Return True or False if any word in a list is in a string

    I am working on some genetic project, I have a column contain all the data with the gene name, which may come in a cluster in a cell, and i have a list which contain the gene i am interested, for example
    Column B (gene list interested)
    PCNA
    GADD45G
    bActin
    p21

    Column A (look up array)
    PCNA;PCNA;p53
    p53;p21
    SOC2;SOC2
    GADD45A


    I want it return true if the string in column A contain any of the gene name in column B.
    I can only come out a formula =isnumber(match(A2,B:B,0))>1 which returns only an exact match. what formula can i use to return if it contains any of the gene listed in column B?

  2. #2
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Return True or False if any word in a list is in a string

    see attatchment
    Attached Files Attached Files
    Azam
    If you want to say Thank you to a member, click the reputation icon (Star) in the left bottom of the post.
    For prompt answer, be descriptive, concise, short, direct, and to-the-point.

  3. #3
    Registered User
    Join Date
    10-05-2011
    Location
    Adelaide
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Return True or False if any word in a list is in a string

    Thankyou so much for your quick reply!!
    but what is the double -- sign in front of isnumber function for?

  4. #4
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Return True or False if any word in a list is in a string

    it is converting logic into number. for detail learining visit

    http://www.xtremevbtalk.com/showpost...33&postcount=5


    It is recommended to always use evaluate formula option in the formula tab in order to understand the formula

  5. #5
    Registered User
    Join Date
    10-05-2011
    Location
    Adelaide
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Return True or False if any word in a list is in a string

    Thank you so much!! problem solved!!

  6. #6
    Registered User
    Join Date
    10-05-2011
    Location
    Adelaide
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Return True or False if any word in a list is in a string

    o no...there is still one problem...how can change the prefix to solved =.="?

  7. #7
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Return True or False if any word in a list is in a string

    Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.


    Kindly also read forum rules

+ 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