+ Reply to Thread
Results 1 to 9 of 9

Extract Words Not Contained in Columns

  1. #1
    Forum Contributor
    Join Date
    06-16-2008
    Posts
    115

    Extract Words Not Contained in Columns

    Hi Everyone,

    I need some help with a formula that I think is possible but I'm not sure.
    Scenario:

    Cell A2 = blue nike womens running shoes
    Cell B2 = nike
    Cell C2 = running shoes

    So, I have a phrase (A2) and I have extracted the the 2 main words into two separate cells (B2 & C2).

    What I 'm looking for is a formula that will extract the remaining words into a 3rd column/cell (D2)

    In this case, Cell D2 = blue womens

    -------------------------------------------------------------------------------------------------------------------------------------------------------

    Ideally, I'd like to extract the remaining words into separate columns/cells as well, 1 for the words that appears before B2 and 1 for words that appear after C2

    In this scenario if A2 = 'blue nike womens running shoes on sale', D2 = 'blue' & E2 = 'on sale'

    The first part is what I really need, so that's what I'm mostly concerned with. the 2nd part is just a wish.

    All replies will be greatly appreciated.

    Thanks!
    Last edited by newbieexceldude; 01-12-2017 at 08:26 PM. Reason: Issue Mostky Resolved

  2. #2
    Registered User
    Join Date
    01-10-2017
    Location
    Pahang, Malaysia
    MS-Off Ver
    2010
    Posts
    41

    Re: Extract Words Not Contained in Columns

    For Part 1
    =TRIM(SUBSTITUTE(SUBSTITUTE(A2,B2,""),C2,""))
    Last edited by ajimieta; 01-12-2017 at 02:07 AM.
    Ajimieta Kuantan

  3. #3
    Forum Contributor
    Join Date
    06-16-2008
    Posts
    115

    Re: Extract Words Not Contained in Columns

    Thanks do much, ajimieta.

    I think this solves 90% of the first scenario. I realized after I posted, sometimes there would be scenarios where there are actually words in between column B & C. So, I need to think about that a little more.

    This is a great start, though. Thanks again.

  4. #4
    Registered User
    Join Date
    01-10-2017
    Location
    Pahang, Malaysia
    MS-Off Ver
    2010
    Posts
    41

    Re: Extract Words Not Contained in Columns

    For Part 2

    D2=LEFT(A3,FIND(B3,A3)-1)
    E2=TRIM(MID(A3,FIND(C3,A3)+LEN(C3),LEN(A3)-FIND(C3,A3)+LEN(C3)))

  5. #5
    Forum Contributor
    Join Date
    06-16-2008
    Posts
    115

    Re: Extract Words Not Contained in Columns

    Thanks again, ajimieta.

    There's a little problem with the 2nd part. The D2 formula works fine, but the E2 formula is a little off and also returned a few errors.

    It looks like it's a word order issue. When the words in B2 & C2 do not match the order of same order as used in the A2 phrase, it doesn't know how to handle it.

    It's a little hard to explain, so I'm attaching a sample.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-10-2017
    Location
    Pahang, Malaysia
    MS-Off Ver
    2010
    Posts
    41

    Re: Extract Words Not Contained in Columns

    Words in C2 not available in the A2, so it will return error because unable to find the requested word in the phrase.... the word tights not available in running tight pants. it is not about the word order...

  7. #7
    Forum Contributor
    Join Date
    06-16-2008
    Posts
    115

    Re: Extract Words Not Contained in Columns

    Thanks. I think its just exact match phrases where you see it.

    Example 1
    leggings running
    D = leggings
    E = running

    When the order is reversed, it returns both words

    Example 2
    workout leggings
    D = workout
    E = leggings

    It returns blanks for both D & E because its the same order as the original phrase

    Not a big deal. And thanks again for your time and help

  8. #8
    Registered User
    Join Date
    01-10-2017
    Location
    Pahang, Malaysia
    MS-Off Ver
    2010
    Posts
    41

    Re: Extract Words Not Contained in Columns

    Ok, understand, actually the formula are based on what you have requested. You need the D return the text before the B, and E after the C.
    Your request was:
    D=Text(s) before the B
    E=Text(s) after the C

    Example:
    A= workout leggings
    B= leggings
    C= workout
    D= the text before the "leggings" will be "workout"
    E=the text after the "workout" will be "leggings"

    Then we change the A to leggings workout, but we maintain the B and C.
    Of course the result become blank in D, because there are no text before the leggings
    also same to the E, because there are no words after the C text.

    Hopefully this can help you.

  9. #9
    Forum Contributor
    Join Date
    06-16-2008
    Posts
    115

    Re: Extract Words Not Contained in Columns

    The blank result was correct. Sorry about any confusion.

+ 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] EXtract First Two Words
    By Deventus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-14-2014, 11:50 AM
  2. Replies: 7
    Last Post: 02-25-2014, 02:00 AM
  3. [SOLVED] looking formula for Extract Specific WORDs in existing LONG Words
    By santosh226001 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-07-2013, 08:21 AM
  4. deleting columns with headers not contained in a list
    By excelfuchs in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-31-2013, 10:11 AM
  5. Extract all but last two words
    By rwl518p in forum Excel General
    Replies: 2
    Last Post: 12-12-2011, 10:06 AM
  6. Replies: 4
    Last Post: 04-10-2011, 08:56 PM
  7. Lookup words contained in a cell
    By will.00 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-30-2009, 07:54 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