+ Reply to Thread
Results 1 to 6 of 6

Replace with whole word match only?

Hybrid View

  1. #1
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Replace with whole word match only?

    Hi... I'm trying to find a way to do a series of replacements (about 200 at the moment) in two separate strings - but I only want the replace to happen where there is an exact match.

    For example, I want to replace "There is 1 line of text here" with "There is ONE line of text here"
    But I want to ignore the "1" in "There are 104 lines of text here"

    This is a small sample of the way I was doing it, but I'm sure there is a much better way

    FinalStringA = Replace(StringA, "1 ", "ONE ")
    FinalStringA = Replace(StringA, "2 ", "TWO ")
    I've tried playing with a leading and trailing space in the value to find, but that isn't working correctly.

    Any idea how to force a whole word match?

    Thanks!

  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
    48,313

    Re: Replace with whole word match only?

    Try:

    Option Explicit
    
    Sub Test()
    
    Dim cell As Range
    
    For Each cell In Range("A1:A10")
        cell = Replace(cell, " 1 ", " ONE ")
        cell = Replace(cell, " 2 ", " TWO ")
    Next 'cell
    
    End Sub
    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 Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: Replace with whole word match only?

    TMS - thanks... I would like to do it in the string if possible, but I do have a process that later tokenizes the string and dumps into a column.... will give that a shot, thank you!!!!

  4. #4
    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
    48,313

    Re: Replace with whole word match only?

    Not really sure what you are trying to achieve or why but maybe this will help

    Sub Test_2()
    
    Dim StringA As String
    Dim FinalStringA As String
    
    Dim vArray
    vArray = Array("There is 1 line of text here", "There are 2 lines of text here")
    Dim vArrayFind
    vArrayFind = Array(" 1 ", " 2 ")
    Dim vArrayReplace
    vArrayReplace = Array(" ONE ", " TWO ")
    
    Dim i As Long
    
    For i = LBound(vArray) To UBound(vArray)
    'There is ONE line of text here
        StringA = vArray(i)
        FinalStringA = Replace(StringA, vArrayFind(i), vArrayReplace(i))
        MsgBox FinalStringA
    Next 'i
    
    End Sub

  5. #5
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: Replace with whole word match only?

    TMS, thank you, will check that out as well.

    To add a little meat to the request, I'm building a tool that compares two documents, which are different versions of transcripts from audio calls, and assigning a score to them based on the content of one "golden" version. At this point in the process, I have two text strings (StringA and StringB) that I'm normalizing before tokenizing and dumping into a pair of columns on the spreadsheet.

    So what I'm trying to do in this phase is remove score deductions we don't really care about, like when Transcript A has "There is 1 line of text here" and the Transcript B has "There is ONE line of text here"

    I have a hidden sheet that has 132 values and their replacements (besides numbers, there are contractions and a few other terms I want to swap out).

    Since these transcripts will be between 20 and 50,000 words each, I think looping through the cells to replace may add a bit too much time, but I haven't finished making the adjustments to the first code bit you provided to test that out.

    Thanks again for your guidance!
    Last edited by JP Romano; 10-29-2018 at 03:25 PM.

  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
    48,313

    Re: Replace with whole word match only?

    Yo should loop through your list of words and use global replace for each pair.

+ 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. Replies: 15
    Last Post: 01-30-2018, 09:13 AM
  2. Excel VBA - Word to search for part of a word and replace full word
    By charlie543 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-24-2016, 02:56 AM
  3. [SOLVED] Find text in word in the cell and replace whole word
    By Grens in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-31-2016, 01:44 PM
  4. [SOLVED] Help with macro to find a specific word and replace the entire cell with the return word.
    By BG1983 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 08-24-2015, 09:40 PM
  5. [SOLVED] Excel macro- Find replace an itallic font word with a regular word?
    By thisisaboutwork in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-08-2013, 01:48 PM
  6. Need help with Excel-to-Word find and replace macro based on word filename
    By EnterTheSerpent in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-16-2013, 12:48 PM
  7. Replies: 6
    Last Post: 02-24-2010, 07:07 AM

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