How to find and replace text in Word document from Excel?
In Word document, we can apply the Find and Replace function to find and replace one text quickly. But, if there are multiple texts need to be found and replaced, enter the text one by one into the Find and Replace feature will be time-consuming. In this case, you can enter the find and replace texts into the list of cells, and with the help of VBA code in Excel to accomplish this job with ease. In this article, I will also introduce a useful feature to batch find and replace texts in multiple Word documents.
Find and replace multiple texts in one Word document from Excel with VBA code
Find and replace multiple texts in multiple Word documents from Excel with VBA code
Find and replace multiple texts in multiple Word documents with a powerful feature
Find and replace multiple texts in one Word document from Excel with VBA code
If you want to find and replace some texts in only one Word file, the following VBA code can do you a favor.
1. In Excel worksheet, create a column containing the texts you want to find and replace, and another column with texts to replace with as below screenshot shown. And then press Alt+ F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. Then, click Insert > Module, copy and paste the below VBA code into the window.
VBA code: Find and replace multiple texts in one Word file
Sub replace_texts_range_of_cells()
'Updateby ExtendOffice
Dim xWordApp As Word.Application
Dim xDoc As Word.Document
Dim xRng As Range
Dim I As Integer
Dim xFileDlg As FileDialog
On Error GoTo ExitSub
Set xFileDlg = Application.FileDialog(msoFileDialogFilePicker)
xFileDlg.AllowMultiSelect = False
xFileDlg.Filters.Add "Word Document", "*.docx; *.doc; *.docm"
xFileDlg.FilterIndex = 2
If xFileDlg.Show <> -1 Then GoTo ExitSub
Set xRng = Application.InputBox("Please select the lists of find and replace texts (Press Ctrl key to select two same size ranges):", "Kutools for Excel", , , , , , 8)
If xRng.Areas.Count <> 2 Then
MsgBox "Please select two columns (press Ctrl key), the two ranges have the same size.", vbInformation + vbOKOnly, "Kutools for Excel"
GoTo ExitSub
End If
If (xRng.Areas.Item(1).Rows.Count <> xRng.Areas.Item(2).Rows.Count) Or _
(xRng.Areas.Item(1).Columns.Count <> xRng.Areas.Item(2).Columns.Count) Then
MsgBox "Please select two columns (press Ctrl key), the two ranges have the same size.", vbInformation + vbOKOnly, "Kutools for Excel"
GoTo ExitSub
End If
Set xWordApp = CreateObject("Word.application")
xWordApp.Visible = True
Set xDoc = xWordApp.Documents.Open(xFileDlg.SelectedItems.Item(1))
For I = 1 To xRng.Areas.Item(1).Cells.Count
With xDoc.Application.Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = xRng.Areas.Item(1).Cells.Item(I).Value
.Replacement.Text = xRng.Areas.Item(2).Cells.Item(I).Value
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchByte = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
xDoc.Application.Selection.Find.Execute Replace:=wdReplaceAll
Next
ExitSub:
Set xRng = Nothing
Set xFileDlg = Nothing
Set xWordApp = Nothing
Set xDoc = Nothing
End Sub
3. After pasting the code, still in the Microsoft Visual Basic for Applications window, click Tools > References, see screenshot:
4. In the popped-out References – VBAProject dialog box, select the Microsoft Word 16.0 Object Library from the list box, see screenshot:
5. Click OK button to close the dialog box, and now, press F5 key to run this code, in the pop up Browse window, select the Word File you want to replace the texts, see screenshot:
6. Then, click OK, in the following dialog box, press Ctrl key to select the original text and new text cells separately that you want to use, see screenshot:
7. And then, click OK button, now, the texts are found and replaced with the new texts in your specified document, and the file is opening as well, you should save it to keep the changes.
Find and replace multiple texts in multiple Word documents from Excel with VBA code
Here, I also create a VBA code for finding and replacing multiple texts in multiple Word documents, please do as this:
1. Open the Excel file that contains two columns of values to replace and replace with as below screenshot shown, and then press Alt+ F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. Then, click Insert > Module, copy and paste the below VBA code into the window.
VBA code: Find and replace multiple texts in multiple Word files
Sub FindReplaceAcrossMultipleWordDocuments()
'Updateby ExtendOffice
Dim xWordApp As Word.Application
Dim xDoc As Word.Document
Dim xRng As Range
Dim I As Integer
Dim xFolderDlg As FileDialog
Dim xFSO As Scripting.FileSystemObject
Dim xFile As File
On Error GoTo ExitSub
Set xFolderDlg = Application.FileDialog(msoFileDialogFolderPicker)
If xFolderDlg.Show <> -1 Then GoTo ExitSub
Set xRng = Application.InputBox("Please select the lists of find and replace texts (Press Ctrl key to select two same size ranges", "Kutools for Excel", , , , , , 8)
If xRng.Areas.Count <> 2 Then
MsgBox "Please select two columns (press Ctrl key), the two ranges have the same size", vbInformation + vbOKOnly, "Kutools for Excel"
GoTo ExitSub
End If
If (xRng.Areas.Item(1).Rows.Count <> xRng.Areas.Item(2).Rows.Count) Or _
(xRng.Areas.Item(1).Columns.Count <> xRng.Areas.Item(2).Columns.Count) Then
MsgBox "Please select two columns (press Ctrl key), the two ranges have the same size.", vbInformation + vbOKOnly, "Kutools for Excel"
GoTo ExitSub
End If
Set xFSO = New Scripting.FileSystemObject
Set xWordApp = CreateObject("Word.application")
xWordApp.Visible = True
For Each xFile In xFSO.GetFolder(xFolderDlg.SelectedItems(1)).Files
If VBA.InStr(xFile.Type, "Microsoft Word") > 0 Then
Set xDoc = xWordApp.Documents.Open(xFile.Path)
For I = 1 To xRng.Areas.Item(1).Cells.Count
With xDoc.Application.Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = xRng.Areas.Item(1).Cells.Item(I).Value
.Replacement.Text = xRng.Areas.Item(2).Cells.Item(I).Value
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchByte = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
xDoc.Application.Selection.Find.Execute Replace:=wdReplaceAll
Next
xDoc.Close wdSaveChanges
End If
Next
xWordApp.Quit
MsgBox "The Find and Replace has been completed", vbInformation + vbOKOnly, "Kutools for Excel"
ExitSub:
Set xRng = Nothing
Set xFolderDlg = Nothing
Set xWordApp = Nothing
Set xDoc = Nothing
End Sub
3. Still in the Microsoft Visual Basic for Applications window, click Tools > References, in the References – VBAProject dialog box, select the Microsoft Word 16.0 Object Library and Microsoft Scripting Runtime options from the list box, see screenshot:
4. After checking the two options, and click OK to close the dialog box, and then, go on pressing the F5 key to execute this code, in the opening Browse window, choose a folder contains the Word documents that you want to perform the find and replace, see screenshot:
5. Click OK button, in the popped-out dialog box, press Ctrl key to select the original text and new text columns separately that you want to use, see screenshot:
6. Finally, click OK, and the original texts are replaced with the new ones across theses files, after completing, a dialog box will pop out as below screenshot shown:
7. Click OK to close the dialog. And you can go to the files to check the converted results.
Find and replace multiple texts in multiple Word documents with a powerful feature
This section, I will talk about how to batch find and replace texts in multiple Word documents from Word instead of Excel. With a powerful tool-Kutools for Word, you can quickly find and replace the specific texts and replace them with new texts in main file, header, footer, comments, etc. and highlight the results as you need.
1. Open one Word file, and then click Kutools Plus > Batch Find and Replace, see screenshot:
2. In the opened Batch Find and Replace dialog box, please do the following operations:
- Click Add button to add the Word files where you want to find and replace texts;
- In the left pane, click Add row from the top ribbon;
- In the inserted field, enter the original text and new text into the Find and Replace columns separately that you want to find and replace. As well, you can specify a color for highlighting the replaced texts as you need.
3. After creating the search criteria, click Replace button to go to the Preview Result tab to view the find and replace results. See screenshot:
4. Then, click Close button, and a prompt box is popped out to remind you if you want to save this scenario, click Yes to save it, and click No to ignore it, see screenshot:
- Find and replace special characters in multiple Word documents;
- Find and replace multiple strings with specific formatting in multiple Word documents;
- Find and replace multiple strings in multiple txt/htm/html files.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
