Results 1 to 6 of 6

Bulk Find and Replace Whole Words, Not Strings

Threaded View

  1. #1
    Registered User
    Join Date
    04-21-2009
    Location
    Puyallup, WA
    MS-Off Ver
    Excel 2010
    Posts
    55

    Bulk Find and Replace Whole Words, Not Strings

    I have an Excel spreadsheet that contains a macro.

    Bulk Find Replace.xlsm

    Column A contains a list of phrases.
    Column F contains a list of values to FIND.
    Column G contains a list of values to REPLACE.

    What I want to achieve using VBA is a find/replace on WHOLE WORDS within column A by FINDING the value in column F and REPLACING with the value in column G.

    Using VBA, I want to append " " to the beginning and ending of each phrase in column A.

    For example, the phrase 'ediscovery institute havasu' would be seen as " "&ediscovery institute havasu&" ".

    Then, all the words in column F would also have " " appended to the beginning and end.

    This way, when the find/replace is performed, it will look for WHOLE WORDS instead of strings and replace those whole words found with the value in column G.

    Lastly, I'd like to then trim the results in column A using VBA then end macro.

    I know how to do this with a formula but I don't know how to do this with VBA.

    This is the code in the current workbook:
      Sub FindReplace()
        Dim Frange As Range
        Dim Fr As Range
        Set Frange = Range("F1", Range("F65536").End(xlUp))
        For Each Fr In Frange
            Columns("A:A").Replace What:=Fr, Replacement:=Fr.Offset(0, 1), LookAt:=xlPart, _
                                   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                                   ReplaceFormat:=False
        Next Fr
    
    End Sub
    Last edited by sweetrevelation; 04-05-2013 at 02:29 PM.

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