How to Use a Word VBA Macro to Find Anchors and Delete Surrounding Text
Question details
The user needs to automate the process of finding specific anchor words in a document and deleting an exact number of characters before and after each match.
- Product
- Word
- Device & OS
- not provided
- Scenario
- Cleaning up text and numbers extracted from a PDF by removing unnecessary characters surrounding specific target words.
- Observed behavior
- The user requires a VBA script that can locate target text, calculate the correct character range to remove before and after, and execute the deletion automatically across the entire document.
Always create a backup copy of your document before running macros that delete text, as VBA deletions usually cannot be easily undone using the standard Undo shortcut.
Use a Custom VBA Macro to Delete Surrounding Text
Run a specific macro script that prompts you for an anchor word and the exact number of characters to delete before and after each match.
This VBA macro searches your active document for a designated word. Once found, it prompts you to specify how many characters to delete immediately preceding and following the anchor word. It loops through the entire document and can be repeated for different anchor words.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the VBA editor, click on 'Insert' in the top menu bar, and select 'Module' to create a blank script window.
Copy and paste the following code into the module window: Dim docRange As Range Dim foundRange As Range Dim strFind As String Repeat: Set docRange = ActiveDocument.Range Selection.HomeKey wdStory strFind = InputBox("Enter the word to find") With Selection.Find Do While .Execute(FindText:=strFind, Forward:=True, MatchWildcards:=False, Wrap:=wdFindStop, MatchCase:=True) Set foundRange = Selection.Range docRange.End = foundRange.Start - InputBox("Enter the number of characters before the found text") docRange.Delete Set docRange = ActiveDocument.Range docRange.Start = foundRange.End + InputBox("Enter the number of characters after the found text") docRange.Delete Loop End With If MsgBox("Do you need to repeat the process?") = vbYes Then GoTo Repeat Else Exit Sub End If
Press F5 or click the 'Run' button (the green play icon) at the top of the editor. A prompt will appear asking for the anchor word, followed by prompts for the character counts to delete.
Run Macros Seamlessly in WPS Office
WPS Office provides robust support for VBA macros, allowing you to automate repetitive document cleanup tasks effortlessly. Easily write, edit, and run scripts to manage text extracted from PDFs while enjoying a familiar, highly compatible word processing environment.
- 1. Download and Install: Download WPS Office from the official website and install it on your computer.
- 2. Open your Document: Launch WPS Writer and open the document containing your extracted PDF text.
- 3. Enable Macros: Navigate to the Developer tab, click on Macros, and run your text-cleanup script safely.
Frequently Asked Questions
Can I undo text deleted by a VBA macro?
Generally, text deleted via a VBA macro cannot be restored using the standard Undo function (Ctrl+Z). Always test your macro on a duplicate copy of your file to prevent accidental data loss.
Why does the macro delete the wrong amount of text?
The character count includes spaces, punctuation, and hidden formatting marks. Be sure to account for these invisible characters when entering the number of characters to delete before or after the anchor word.
How do I enable the Developer tab to use macros?
Go to File > Options > Customize Ribbon, and check the box next to 'Developer'. This will add the Developer tab to your main ribbon, giving you access to the Visual Basic editor and macro execution tools.
Can I delete whole sentences instead of character counts?
Yes, but you will need a different macro that specifically targets the `Sentences` collection or uses wildcards to identify text between punctuation marks, rather than using a fixed character count deletion method.
