How to Remove Underlined Spaces Around Word Hyperlinks via VBA Macro
Question details
The user needs a VBA macro to automatically remove unwanted underline formatting from spaces surrounding hyperlinks in a Word document.
- Product
- Microsoft Word
- Device & OS
- not provided
- Scenario
- A Word document was converted from an XML file, causing the spaces before and after hyperlink text to erroneously display with underlines. The user wants to bulk-remove this formatting programmatically.
- Observed behavior
- Spaces surrounding the hyperlink text display an underline identical to the hyperlink itself, which looks messy and requires a VBA macro to trim the spaces and set the underline formatting to none.
Before running the macro, ensure you have enabled the Developer tab in your word processor to access the Visual Basic Editor, and save a backup copy of your document to prevent accidental data loss during the script execution.
Use a VBA Macro to Trim Spaces and Clear Underline Formatting
Create and run a custom VBA script that loops through the document's hyperlinks, trims leading and trailing spaces, and removes the underline property from adjacent space characters.
When a Word document is generated from an XML file, whitespace characters often mistakenly inherit the hyperlink's underline formatting. To resolve this programmatically, the macro needs to target both the hyperlink's display text and its underlying URL address.
The most effective logic involves looping through all hyperlinks, using 'LTrim' and 'Left' functions to identify and process leading spaces first. After the leading spaces are handled, the script should use 'Trim' and 'Right' functions to remove trailing spaces, finally applying 'wdUnderlineNone' to any leftover blank characters.
In your Word document, press 'Alt + F11' on your keyboard to launch the Visual Basic for Applications (VBA) Editor.
In the VBA Editor, click on 'Insert' in the top menu bar, then select 'Module' to open a blank coding window.
Write a 'For Each' loop to iterate through 'ActiveDocument.Hyperlinks'. Within the loop, use the 'LTrim' and 'Left' functions to check for spaces at the beginning of the hyperlink's address and displayed text. Trim these spaces off before moving to the end.
Continue the loop by using the 'Trim' and 'Right' functions to evaluate the end of the hyperlink. Strip away the trailing spaces. Finally, target the space characters just outside the updated hyperlink range and set their 'Font.Underline' property to 'wdUnderlineNone'.
Click inside your completed code and press 'F5', or click the green 'Run' arrow in the toolbar. The macro will automatically clean up all the hyperlinks in your active document.
Format Documents and Manage Hyperlinks Easily with WPS Office
Tired of dealing with complex XML conversion glitches and writing custom VBA macros just to clean up document formatting? WPS Office is a highly compatible, free alternative to Microsoft Word that makes document editing and formatting straightforward.
- 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your computer.
- 2. Open Your Document: Launch WPS Writer and open your converted XML or DOCX file directly. The formatting remains beautifully intact.
- 3. Adjust Formatting Easily: Use the familiar ribbon interface to manually clear underlines from spaces, or utilize WPS Office's advanced features for faster document cleanup.
Frequently Asked Questions
Why do spaces around hyperlinks get underlined during an XML conversion?
During XML to Word conversions, parsing engines often misinterpret formatting tags, mistakenly applying the hyperlink text style (which includes underlines) to adjacent whitespace characters. This results in the blue underline extending past the actual clickable text.
How can I remove an underline from a single space without using a macro?
If you only have a few hyperlinks to fix, simply highlight the specific underlined space with your mouse. Then, go to the 'Home' tab and click the 'Underline' button (or press Ctrl+U) to toggle the formatting off for that specific character.
Will trimming the hyperlink spaces break the URL destination?
No. Using the VBA 'LTrim' and 'Trim' functions on the hyperlink address safely removes accidental whitespace. Web addresses generally do not contain spaces, so this process actually cleans up the URL while keeping it fully functional.
Does WPS Writer support running macros?
Yes, WPS Office supports VBA macros in its Professional and Premium versions, allowing you to run your existing Microsoft Word scripts to automate document formatting tasks.
