+ Reply to Thread
Results 1 to 3 of 3

Macro to search cells for substring and replace contents of cell if substring is found

  1. #1
    Registered User
    Join Date
    02-11-2010
    Location
    Maryland, USA
    MS-Off Ver
    Excel 2007
    Posts
    83

    Macro to search cells for substring and replace contents of cell if substring is found

    Hey all,

    I'm working on a project where I need to search the contents of a couple different columns of data (columns K & L in the "3D Printing Patents" tab of the attached workbook) for substrings contained on a separate worksheet (COL A of the "Replace List" worksheet). When a substring is found in a cell in columns K or L, I'd like to replace the entire contents of that cell with the string that correlates to the substring found (COL B in the "Replace List" worksheet.)

    I need the operation to NOT be case sensitive. I have a macro in the attached worksheet that works to replace with a particular string that is listed there in the macro, but I'm admittedly pretty inexperienced with VBA and don't know how to modify it to do what I need it to do. Can you guys/gals help?

    Thanks,

    Rob
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Macro to search cells for substring and replace contents of cell if substring is found

    Maybe:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    02-11-2010
    Location
    Maryland, USA
    MS-Off Ver
    Excel 2007
    Posts
    83

    Re: Macro to search cells for substring and replace contents of cell if substring is found

    Thanks John!

    I think this works to replace the specific substring within a cell, but it leaves the rest of the text that was present in the cell before the macro runs. I'd like it to completely replace the entire contents of the cell if possible.

    The next hiccup... I also need to create a third column (I'll insert COL M "Consolidated Assignee" in the spreadsheet I attached above). Then, if the contents of cell K or L matches any cell in the "Text To Insert" column (COL B) of the "Replace List" worksheet, I'd like to copy the value of the matching cell in "Text To Insert" to the accompanying "Consolidated Assignment" cell in COL M.

    I'm probably making it sound more complicated than it needs to be. So I'm attaching an updated version of the spreadsheet. The first worksheet shows the original table. The second worksheet shows what I'd like the result of my macro to be. And the third worksheet is the "Replace List." I noted that there are some instances where I don't have a matching entry in the "Replace List." The result should be to leave all of the cells unedited (See ROW 5). There are also instances where one of COL K or COL L are blank. The result should be to copy the value that is present (See rows 4 & 7).

    I really need help with this. I'm totally lost.

    Thank you,

    Rob
    Attached Files Attached Files

+ 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. macro to copy cell contents then remove substring of contents in new cell - Help
    By three_jeeps in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-25-2015, 01:19 AM
  2. Search substring within range, report the substring found
    By Brooke1578 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-20-2012, 03:56 PM
  3. Replies: 2
    Last Post: 06-29-2012, 07:02 AM
  4. Find and Replace Substring
    By ncalenti in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-14-2012, 06:54 PM
  5. [SOLVED] Extract Substring, then Ignore that Substring, while collecting data from Other substrings
    By Sameki121 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-17-2012, 05:21 PM
  6. Splitting a row into two when a certain substring is found
    By lifeseeker1019 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-17-2011, 11:32 PM
  7. Find and Replace the character in a substring.
    By saikumar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-14-2009, 04:44 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