+ Reply to Thread
Results 1 to 6 of 6

VBA to Search a Text String, Find a Value listed in another column and replace the text

  1. #1
    Registered User
    Join Date
    11-08-2012
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    2

    VBA to Search a Text String, Find a Value listed in another column and replace the text

    Hey everyone I have a problem that I really could use your expertise on....

    My problem is this... In Excel 2010 I have a column (let’s say column A) that is a series of text strings. What I need to do is similar to a vlookup+find/replace.

    In column B I have a values that I want to find and then replace with the values in column C. The key is that I need the entire text sting to stay intact with only the specific value being changed. I have basic knowledge of VBA but I know it can be done. VBA is an ideal solution because my volume of text strings can be 50k plus lines... and I think this is beyond what can be written with standard Excel Formulas. Below is an example and I have attached an sheet with a more detailed example if you need it.

    Thanks for the help in advance!!!

    Example:
    Column A (text string)-----Column B (Find Text)-----Column C (Replace with)-----Column D (next text string)
    text1 text2 Denver text3 text4-----Denver------CITY-----text1 text2 CITY text3 text4

    FINAL NOTE: I do NOT really need an “output” or Column D if it could simply replace the value in Column A while keeping the other part of the string intact that would work too.... Thanks again I look forward to seeing what you guys come up with!
    Attached Files Attached Files
    Last edited by Jschrod303; 11-16-2012 at 08:08 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: VBA to Search a Text String, Find a Value listed in another column and replace the tex

    Record a macro to do a global replace for the first pair of values. Then build a loop to go through each pair of values.

    It would be a simple "For Each cell in Range("B2:B" & LastRow)" ... your find/replace code ... Next ' cell.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: VBA to Search a Text String, Find a Value listed in another column and replace the tex

    hi Jschrod303, welcome to Excelforum, option, to go a bit further on TMShucks advice, press Run button, amend to your real data layout if required
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-08-2012
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: VBA to Search a Text String, Find a Value listed in another column and replace the tex

    WaterSev you are awesome thank you so much for help!!! PERFECT

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: VBA to Search a Text String, Find a Value listed in another column and replace the tex

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: VBA to Search a Text String, Find a Value listed in another column and replace the tex

    Thanks for the rep. Not sure it's deserved; I just threw in the idea but watersev did all the work.

    Nevertheless, gratefully received.

    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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