Find and Replace Hyperlinks (url links) in Excel

Excel has some strange behaviors, one of them is that the “find & replace” function doesn’t work with hyperlinked content.   In order to overcome this you need to use a simple macro. Here are the steps to take:

1. While on the spreadsheet you wish to edit press ALT + F11 – this will open the Visual Basic Application Window
2. Click Insert > Module, and paste the following macro in the Module window
   
   Sub ReplaceHyperlinks() 'Updated20170724 Dim Ws As Worksheet Dim xHyperlink As Hyperlink Dim xOld As String, xNew As String xTitleId = "toolsforExcel" Set Ws = Application.ActiveSheet xOld = Application.InputBox("Old text:", xTitleId, "", Type:=2) xNew = Application.InputBox("New text:", xTitleId, "", Type:=2) Application.ScreenUpdating = False For Each xHyperlink In Ws.Hyperlinks xHyperlink.Address = Replace(xHyperlink.Address, xOld, xNew) Next Application.ScreenUpdating = True End Sub

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   Sub ReplaceHyperlinks() 'Updated20170724 Dim Ws As Worksheet Dim xHyperlink As Hyperlink Dim xOld As String, xNew As String xTitleId = "toolsforExcel" Set Ws = Application.ActiveSheet xOld = Application.InputBox("Old text:", xTitleId, "", Type:=2) xNew = Application.InputBox("New text:", xTitleId, "", Type:=2) Application.ScreenUpdating = False For Each xHyperlink In Ws.Hyperlinks     xHyperlink.Address = Replace(xHyperlink.Address, xOld, xNew) Next Application.ScreenUpdating = True End Sub

3. Run this Macro by pressing F5 – fill the old and new text in the popup boxes and it will make the replace