How to Find and Replace Text in Word Document Using Excel VBA (5 Methods)

Method 1 – Find and Replace a Text String in a Word Document Using Excel VBA

• Open the Word document that contains the list of products. Save the file as Product.docm.
• Go to the Developer tab and select Visual Basic to open the Visual Basic Editor.

• Click the Insert tab and choose Module to create a new module.

• To execute the code through the Word document, go to Tools > References and enable the Microsoft Word 16.0 Object Library option.

• Press OK.

• Enter the following VBA code

Sub findandreplaceword1()

Dim book1 As Word.Application
Dim sheet1 As Word.Document
Set book1 = CreateObject("word.application")
book1.Visible = True
Set sheet1 = book1.Documents.Open("C:\Users\Mima\Downloads\Product.docm")

With sheet1.Content.Find
.Text = "Apple"
.Replacement.Text = "Cherry"
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With

End Sub

In this code, we declare appWord as a Word application object and doc as a Word document object. We open the specified document and use the With statement to find the text string “Apple” and replace it with “Cherry.” The .Wrap = wdFindContinue ensures the operation continues until the end of the document, and .Execute Replace:=wdReplaceAll replaces all occurrences.

• Press F5 to run the code. The word Cherry will replace Apple in the product list.

Method 2 -Find and Replace a Text String in a Word Document with Formatting Changes

• Follow Step 1 from Method 1.
• Enter the following VBA code:

Sub findandreplaceword2()

Dim book1 As Word.Application
Dim sheet1 As Word.Document
Set book1 = CreateObject("word.application")
book1.Visible = True
Set sheet1 = book1.Documents.Open("C:\Users\Mima\Downloads\Product.docm")
sheet1.Content.Find.ClearFormatting
sheet1.Content.Find.Replacement.ClearFormatting

With sheet1.Content.Find
.Text = "Apple"
With .Replacement
    .Font.Bold = True
    .Text = "Cherry"
End With
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = True
.Execute Replace:=wdReplaceAll
End With

End Sub

In this code, we again declare appWord and doc. We find the text Apple, replace it with Cherry, and make the replacement text bold using .Font.Bold = True.

• Press F5 to run the code. You’ll see the bold font Cherry replacing Apple in the product list of the Word document.

Method 3 – Find and Replace Text Strings from a Specific Paragraph in Word Document

In this method, we’ll search for the text string app throughout the first paragraph of a given passage and replace it with the string Application using VBA code.

• Follow Step 1 from Method 1.
• Enter the following VBA code:

Sub findandreplaceword3()

Dim book1 As Word.Application
Dim sheet1 As Word.Document
Set book1 = CreateObject("word.application")
book1.Visible = True
Set sheet1 = book1.Documents.Open("C:\Users\Mima\Downloads\Paragraph.docm")

With sheet1.Content.Paragraphs(1).Range.Find
        .Text = "App"
        .Replacement.Text = "Application"
        .Forward = True
        .Wrap = wdFindStop
        .Format = False
        .MatchCase = False
        .Execute Replace:=wdReplaceAll
End With

End Sub

In this code, we declare appWord as a Word application object and doc as a Word document object. We open the specified document and use the With statement to find the text string “App” within the first paragraph. We then replace it with “Application.” The .Wrap = wdFindStop ensures that the operation stops after the first occurrence, and .Execute Replace:=wdReplaceAll replaces all instances.

• Press F5 to run the code. You’ll see the text string Application replacing app in all occurrences within the first paragraph of the Word document. Note that the text will not be replaced in the second paragraph.

Method 4 – Find and Replace a Range of Text Strings in a Word Document Using InputBox

In this method, we’ll simultaneously replace the text strings Orange, Guava, Broccoli, and Potatoes using a VBA code with the help of the InputBox function.

• Follow Step 1 from Method 1.
• Enter the following VBA code:

Sub findandreplaceword4()

Dim book1 As Word.Application
Dim sheet1 As Word.Document
Dim j As Integer
Dim oldstring, newstring As String
Dim oldstringArr, newstringArr As Variant
Set book1 = CreateObject("word.application")
Set sheet1 = book1.Documents.Open("C:\Users\Mima\Downloads\Product.docm")
book1.Visible = True
oldstring = InputBox("Write the text strings to be replaced ")
newstring = InputBox("Write the names of the new text strings")
oldstringArr = Split(oldstring, ",")
newstringArr = Split(newstring, ",")

If UBound(oldstringArr) <> UBound(newstringArr) Then
MsgBox "The number of text strings to find and replace must be equal"
Exit Sub
End If

For j = 0 To UBound(oldstringArr)
      With sheet1.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .Text = oldstringArr(j)
            .Replacement.Text = newstringArr(j)
            .Format = False
            .Execute Replace:=wdReplaceAll
       End With
Next
Application.ScreenUpdating = True

End Sub

In this code:

• We declare appWord as a Word application object and doc as a Word document object.
• We open the specified document.
• Two input boxes will appear: one for the old strings (assigned to oldstring) and the other for the new strings (assigned to newstring).
• We split the input strings into arrays (oldstringArr and newstringArr) using commas.
• If the array sizes are not equal, a message box will notify the user.
• The For loop replaces each old string with the corresponding new string.

• Press F5 to run the code.

• You’ll see the new text strings (Clementine, Cabbage, Okra, Eggplant) replacing the old strings (Guava, Broccoli, Potatoes, Orange) in the Word document.
• Press OK.

Method 5 – Find and Replace a Range of Text Strings in a Word Document

In this method, we’ll identify a range of text strings within the Word document and replace them with our desired strings using VBA code. To achieve this, we’ve listed the old strings (Guava, Broccoli, Potatoes, Orange) and their corresponding new strings (Clementine, Cabbage, Okra, Eggplant) in an Excel worksheet.

• Follow Step 1 from Method 1.
• Enter the following VBA code:

Sub findandreplaceword5()

Dim book1 As Word.Application
Dim sheet1 As Word.Document
Dim i As Integer
Set book1 = CreateObject("word.application")
book1.Visible = True
Set sheet1 = book1.Documents.Open("C:\Users\Mima\Downloads\Product.docm")

For i = 4 To 7
With sheet1.Content.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .MatchWildcards = False
        .Wrap = wdFindContinue
        .Text = Cells(i, 2)
        .Replacement.Text = Cells(i, 3)
        .Execute Replace:=wdReplaceAll
End With
Next i

End Sub

Explanation:

• We declare appWord as a Word application object and doc as a Word document object.
• Open the specified document.
• The For loop iterates through rows 4 to 7 of the Excel worksheet.
• .Text = Cells(i, 2) retrieves the value of the old strings from Column B.
• .Replacement.Text = Cells(i, 3) sets the value of the new strings from Column C.

• Press F5 to run the code. You’ll see the new text strings (Clementine, Cabbage, Okra, Eggplant) replacing the old strings (Guava, Broccoli, Potatoes, Orange) in the Word document.

Download Workbook

You can download the practice workbook from here:

Related Articles

• How to Find and Replace from List with Macro in Excel
• Excel VBA to Find and Replace Text in a Column
• How to Find and Replace Multiple Values in Excel with VBA
• Find and Replace a Text in a Range with Excel VBA
• Excel VBA to Replace Blank Cells with Text
• Excel VBA: Replace Character in String by Position
• Excel VBA: How to Replace Text in String