Batch Find and Replace Specific Text in Hyperlinks

Hyperlinks in Excel can point to web addresses, files, email addresses, or other locations. Sometimes, you might need to update multiple hyperlinks simultaneously. Here's a step-by-step guide on how to batch find and replace specific text in hyperlinks.

---

Step 1: Open Your Excel File

• Double-click on the Excel file to open it. Ensure it contains the hyperlinks you want to change.

Step 2: Press ALT + F11 to Access the Visual Basic for Applications (VBA) Editor

• This will open the VBA environment where you can add custom code to perform specific tasks in Excel.

Step 3: Insert a New Module

• In the VBA editor, right-click on "VBAProject (YourFileName)" in the left sidebar.
• Hover over "Insert" and select "Module" from the dropdown menu. This will add a new module where you can paste your code.

Step 4: Copy and Paste the VBA Code Below into the Module Window

Sub BatchReplaceHyperlinkText()
    Dim hl As Hyperlink
    Dim sFind As String
    Dim sReplace As String
    Dim ws As Worksheet

    'Specify text to find and text to replace
    sFind = InputBox("Enter the text you want to find:")
    sReplace = InputBox("Enter the replacement text:")

    'Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        'Loop through each hyperlink in the worksheet
        For Each hl In ws.Hyperlinks
            'Replace the found text with the specified text
            hl.Address = Replace(hl.Address, sFind, sReplace)
        Next hl
    Next ws
End Sub

Step 5: Close the VBA Editor

• You can simply click on the 'X' button or go to "File" and then "Close and Return to Microsoft Excel".

Step 6: Run the Macro

• Press ALT + F8 to open the "Macro" window.
• You'll see a list of available macros. Choose "BatchReplaceHyperlinkText" from the list and click "Run."

Step 7: Provide the Find and Replace Text

• A pop-up will ask you for the text you wish to find in the hyperlinks. Enter the text and click "OK."
• Another pop-up will ask for the replacement text. Enter the desired replacement text and click "OK."
• The macro will then process each hyperlink in your workbook, and if it finds the specified text, it will replace it with the provided replacement text.

Step 8: Save Your Workbook

• To ensure your changes are retained, click on "File" then "Save" or press CTRL + S.

---

Before running any macros, especially those that modify content, it's always a good idea to save a backup of your Excel file. This way, if something goes wrong, you can easily revert to the original state. Congratulations! You have now learned how to batch find and replace specific text in hyperlinks in Microsoft Excel.