+ Reply to Thread
Results 1 to 6 of 6

searching list of words in a cell

Hybrid View

nazaninibb searching list of words in a... 04-26-2015, 11:49 AM
JBeaucaire Re: searching list of words... 04-26-2015, 12:26 PM
nazaninibb Re: searching list of words... 04-26-2015, 01:04 PM
JBeaucaire Re: searching list of words... 04-26-2015, 01:14 PM
nazaninibb Re: searching list of words... 04-26-2015, 02:08 PM
JBeaucaire Re: searching list of words... 04-26-2015, 10:06 PM
  1. #1
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: searching list of words in a cell

    You don't indicate what the results should be. Your sample data does not appear to include any matches, either.

    If you were to put this formula in C2 and copy down, the LAST word from column A that existed in the text string in column B will appear in C. Only one result per cell.

    =LOOKUP(2, 1/(ISNUMBER(SEARCH($A$1:$A$16, B1))), $A$1:$A$16)
    Last edited by JBeaucaire; 04-26-2015 at 12:29 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  2. #2
    Registered User
    Join Date
    04-26-2015
    Location
    Tehran
    MS-Off Ver
    2007
    Posts
    58

    Re: searching list of words in a cell

    thanks for the help. I actually know that words in column A are located in front of "GENE=" or "lOCUS=".
    using your formula a lot of unwanted matches were found. for example for word "nasE" the row containing "dehydrogenase" word was reported as match.
    (Some cells do not have any "GENE=" or "lOCUS="). My first priority is the match located in front of "GENE=" though.
    Can you suggest me any formula that only searches the words in front of "GENE=" or "lOCUS="?

    here is an example of a cell content in column B:
    1121158 // gi|1121158|ref|NC_002758.2|NC_002758.2(GI:57634611):1234382-1236618(+) Staphylococcus aureus subsp. aureus Mu50, GENE=pbpA PRODUCT=penicillin-binding protein 1 LOCUS=SAV1181 // ncbi_bacterial // 20 // --- /// 1123855 // gi|1123855|ref|NC_002745.2|NC_002745.2(GI:29165615):1158053-1160289(+) Staphylococcus aureus subsp. aureus N315, GENE=pbpA PRODUCT=penicillin-binding protein 1 LOCUS=SA1024 // ncbi_bacterial // 20 // --- /// 3236102 // gi|3236102|ref|NC_002951.2|NC_002951.2(GI:57650036):1197639-1199875(+) Staphylococcus aureus subsp. aureus COL, GENE=pbp1 PRODUCT=penicillin-binding protein 1 LOCUS=SACOL1194 // ncbi_bacterial // 20 // ---

    best
    Nazanin

+ 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. [SOLVED] Searching a cell for multiple words
    By squigman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-16-2013, 02:57 AM
  2. Replies: 3
    Last Post: 08-14-2013, 10:38 AM
  3. Replies: 3
    Last Post: 11-28-2012, 04:00 AM
  4. Searching for multiple words in a cell
    By CU2011 in forum Excel General
    Replies: 12
    Last Post: 05-05-2011, 11:48 AM
  5. [SOLVED] Searching a cell with multiple words
    By nervous_pilchard in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-21-2010, 10:17 AM

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