Excel Substitute or Find and Replace or Macro Solution

I have a rather large spread sheet where I would like to have Excel find specific words in the sheet (among thousands of sentences) and replace them with my chosen words.  I use the Substitute Function but it only has up to 8 words I can substitute (find and replace) – I would like to have the ability to seek and replace 20-50 words at a time.   I am trying to figure out a way I can run this in a macro (or other solution).  
Book2.xlsx
mmmcdeeAsked:
Who is Participating?
 
StephenJRConnect With a Mentor Commented:
Yes, thank you.
Sub x()

Dim oRgx As Object, rCell As Range, v, w

Set oRgx = CreateObject("VBScript.RegExp")

v = Range("D6:D14")
w = Join(Application.Transpose(v), "|")

With oRgx
    .Global = True
    .Pattern = w
    For Each rCell In Range("B6", Range("B6").End(xlDown))
        rCell.Offset(, 5) = .Replace(rCell, Range("D4").Value)
    Next rCell
End With

End Sub

Open in new window

0
 
StephenJRCommented:
Well code could loop through a series of words, or regular expressions perhaps. Are all the words being replaced by the same word or different ones?
0
 
mmmcdeeAuthor Commented:
Different Words. Replacing words within sentences.  I created a sample attached sheet.
0
 
mmmcdeeAuthor Commented:
Correction:  All of the words are being replaced by the same word.
0
 
mmmcdeeAuthor Commented:
Awesome Job
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.