+ Reply to Thread
Results 1 to 6 of 6

Remove the words from Cell A1 that are in Column B

  1. #1
    Registered User
    Join Date
    01-15-2016
    Location
    Poland
    MS-Off Ver
    2013
    Posts
    11

    Remove the words from Cell A1 that are in Column B

    I want to remove the words from Cell A1 that are in Column B

    I have cell A1 with string:
    very funny black horse with red bow

    Column B:
    very
    black
    red

    I tried substitute function: =SUBSTITUTE(A1;B1:B3;" ")
    but when using substitute function i cannot put column i can put only one cell


    I have column with arround 1000 words like A1 and 200 words like in Column B and i want to remove the duplicate words.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Remove the words from Cell A1 that are in Column B

    Hi Grens,

    Welcome to the Forum! Is a VBA solution acceptable? If so:
    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Remove the words from Cell A1 that are in Column B

    Can't have array references for a search. If your list in column B is only 3 records long, you could try:

    Please Login or Register  to view this content.
    This method gets more tedious the more entries there are. Otherwise, you may want to go the User Defined Function method.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  4. #4
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,527

    Re: Remove the words from Cell A1 that are in Column B

    Here's an example of a UDF that mcmahobt and xladept were referring to.
    It's installed and working in the attached file in cell B1.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    As a reminder, once your original request has been fulfilled please mark this thread as SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

    If you are happy with my help, then please consider clicking the add reputation button in the lower left hand corner of this post.

  5. #5
    Registered User
    Join Date
    01-15-2016
    Location
    Poland
    MS-Off Ver
    2013
    Posts
    11

    Re: Remove the words from Cell A1 that are in Column B

    Works great exacly what i was looking for. Now i will try to undarstand how this function exacly works. Thanks a lot

  6. #6
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,527

    Re: Remove the words from Cell A1 that are in Column B

    Quote Originally Posted by Grens View Post
    Works great exacly what i was looking for. Now i will try to undarstand how this function exacly works. Thanks a lot
    Thanks for the rep. points.

    The function is pretty simple, it puts all the words you want to filter out into an array and then loops the array and uses replace to replace them one at a time with an empty string. If there is only one word it goes to the else part of the code.

+ 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. How to Remove Less than 4 Words in a Cell
    By jeteowo in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-19-2014, 08:50 AM
  2. Remove Stop Words from a column containing 16000 rows of sentences
    By Abhayrajify in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-30-2013, 07:14 PM
  3. [SOLVED] Highlight a set of data from column D and remove the same words from Column E
    By rampulaparthi in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-01-2013, 04:10 AM
  4. How to remove two specific words from each cell in a column
    By pahari75 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-10-2013, 06:29 PM
  5. How to Remove More or Less than 3 Words in a Cell
    By yogamast12 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-25-2012, 02:50 AM
  6. Is it Possible to Delete or Remove Words from a Column?
    By randolph188 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 10-28-2009, 03:48 PM
  7. Replies: 6
    Last Post: 02-21-2009, 08:13 PM

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