+ Reply to Thread
Results 1 to 4 of 4

looking to find 1 of 2 words in a cell in column B and return the word found in the same

Hybrid View

  1. 05-27-2014, 01:02 PM #1
    Rob69mfj
    Rob69mfj is offline
    Registered User
    Join Date
    05-14-2014
    MS-Off Ver
    Excel 2013
    Posts
    15

    looking to find 1 of 2 words in a cell in column B and return the word found in the same

    looking to find 1 of 2 words in a cell in column B and return the word found in the same row in column E. This seemed easy but I am not having any luck.

    the cells in column B have several words in them but I am looking for 2 specific words "PLAT" and "ORIG". If the word is not in the cell, it should show a blank cell in column E in the same row, otherwise one of the 2 words should be in that row in column E. A VBA loop would be ideal but a formula that can do it might work as well

    any help is appreciated
    Reply With QuoteRegister To Reply
  2. 05-27-2014, 01:07 PM #2
    nathansav
    nathansav is offline
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: looking to find 1 of 2 words in a cell in column B and return the word found in the sa

    =if(iserr(find("plat",b1,1)),if(iserr(find("orig",b1,1)),"",e1),e1)

    This will return from e if plat or orig are found in b, I didn't understand the bit about being in column E
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me
    Reply With QuoteRegister To Reply
  3. 05-27-2014, 01:16 PM #3
    Rob69mfj
    Rob69mfj is offline
    Registered User
    Join Date
    05-14-2014
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: looking to find 1 of 2 words in a cell in column B and return the word found in the sa

    Columns C and D have values, E would be the next blank column. I just wanted the word put in that column. My original formula was similar to this one, but it is not showing the "PLAT" or the "ORIG", only blank cells are showing up.
    Reply With QuoteRegister To Reply
  4. 05-27-2014, 01:17 PM #4
    Ron Coderre
    Ron Coderre is offline
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: looking to find 1 of 2 words in a cell in column B and return the word found in the sa

    Try this regular formula, copied down
    E2: =IFERROR(INDEX({"plat";"orig"},MATCH(1,INDEX(COUNTIF(B2,{"*plat*";"*orig*"}),0),0)),"")
    An alternative is to put your list of words in a range:
    I1:I2 contains
    plat
    orig

    Then you could use this regular formula
    E2: =IFERROR(INDEX($I$1:$I$2,MATCH(1,INDEX(COUNTIF(B2,"*"&$I$1:$I$2&"*"),0),0)),"")
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. A word search to return words found in given fields
    By JonBrett in forum Excel General
    Replies: 2
    Last Post: 03-28-2014, 10:16 PM
  2. Which function to find a words in word string and return them
    By majo. in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-27-2013, 09:08 AM
  3. Search Value in Cell, Return Whole Word to Left or Right of Found Value
    By sweetrevelation in forum Excel General
    Replies: 4
    Last Post: 05-07-2012, 10:53 PM
  4. [SOLVED] Find and return a word, in a list of words, that is in a single cell.
    By nicholas.jacka in forum Excel General
    Replies: 2
    Last Post: 03-07-2012, 05:13 AM
  5. Using "Find" to return results if a word is found within multiple words in a cell
    By pylauzier in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-01-2011, 01:37 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1