+ Reply to Thread
Results 1 to 8 of 8

Removing text strings based on multiple matches

  1. #1
    Registered User
    Join Date
    10-20-2018
    Location
    UK
    MS-Off Ver
    2018
    Posts
    7

    Post Removing text strings based on multiple matches

    I'm trying to remove multiple snippets of text from one cell based on whether it matches a selection from another cell.

    I might be able to figure this out by splitting it into loads of separate cells, but that seems messy. I also tried using Find, Match and Substitute but couldn't work out how to get this all in one formula. Is it possible?

    I'm trying to get a result like column C here:

    Annotation 2019-06-27 095133.png
    Attached Files Attached Files

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,099

    Re: Removing text strings based on multiple matches

    I ask in advance (just in case) - Is it possibility that 3 words appear in middle column?

    as macro:

    Please Login or Register  to view this content.
    Last edited by KOKOSEK; 06-27-2019 at 05:24 AM.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Registered User
    Join Date
    10-20-2018
    Location
    UK
    MS-Off Ver
    2018
    Posts
    7

    Re: Removing text strings based on multiple matches

    Sorry to be awkward, but I am trying to do this as a formula rather than a macro and with every match. That's a great macro though, thanks

  4. #4
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,099

    Re: Removing text strings based on multiple matches

    Quote Originally Posted by kaiming View Post
    Sorry to be awkward, but I am trying to do this as a formula rather than a macro and with every match. That's a great macro though, thanks
    Don't worry. You decide.
    I ask once more anyway, is it possibility that 3 words appear in middle column?

  5. #5
    Registered User
    Join Date
    10-20-2018
    Location
    UK
    MS-Off Ver
    2018
    Posts
    7

    Re: Removing text strings based on multiple matches

    Sorry, I meant that I do need it to check more than 3 words in the middle column. I want to find every match.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,031

    Re: Removing text strings based on multiple matches

    I am not familiar with your profiled Excel version 2018.

    Banking on the possibility it has the TEXTJOIN function try this (as in attached).

    Two helper columns to determine the count of foods in columns A:B.
    In D2 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In E2 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then array enter in C2 and fill down.

    If you arenít familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If this is working at your end please let me know. That formula can be shortened with some additional helper columns.
    Dave

  7. #7
    Registered User
    Join Date
    10-20-2018
    Location
    UK
    MS-Off Ver
    2018
    Posts
    7

    Re: Removing text strings based on multiple matches

    Thanks, FlameRetired. I've only just got around to checking your response, but that's brillliant. It'll take me a little while to wrap my head around how that works. Thank you!

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,031

    Re: Removing text strings based on multiple matches

    You are welcome. Thank you for the feedback, added rep and marking your thread Solved.

+ 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] Retrieving multiple text strings based on multiple criteria (formula update)
    By McKneezy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-24-2019, 08:52 PM
  2. Formula to Populate Multiple Cells Based on Text strings in Another Cell
    By RASoff in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-18-2014, 11:52 AM
  3. Formula for conditional formatting based on any of multiple text strings?
    By klenatron in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-28-2013, 03:31 PM
  4. Removing various different text, from varying length text strings
    By cloudwalking in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-04-2013, 10:37 AM
  5. [SOLVED] Copy and Paste entire row based on multiple text strings
    By Mr_Kite in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-19-2012, 09:51 AM
  6. Replies: 1
    Last Post: 03-20-2012, 02:54 PM
  7. Replies: 5
    Last Post: 03-06-2008, 01:27 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