Hyperlink Search and Replace in Microsoft Excel 1.2 2008/07/17 10:20:39 GMT-5 2008/08/04 13:29:49.265 GMT-5 Trent Kelly advplc@yahoo.com Trent Kelly advplc@yahoo.com Excel hyperlink macro Microsoft URL VBA visual basic for applications Many MS Excel users put hyperlinks in spreadsheet cells. If there is need to update a large number of hyperlink addresses in a spreadsheet due to reorganization or renaming of a server, it can be very time consuming to edit and update all the hyperlinks. This example code will locate and replace a designated group of characters in all the anchor text hyperlink addresses within a spreadsheet. If the URL has been typed directly into the cell, it can be found and replaced with the normal Excel search and replace function (Ctrl-h). If anchor text has been typed into the cell and then turned into a hyperlink, this code can search and replace the hidden URL's that normal search and replace will not find. Public Sub ReplaceHyperlinkURL(FindString As String, ReplaceString As String) Dim LinkURL, PreStr, PostStr, NewURL As String Dim FindPos, ReplaceLen, URLLen As Integer Dim MyDoc As Worksheet Dim MyCell As Range On Error GoTo ErrHandler Set MyDoc = ActiveSheet For Each MyCell In MyDoc.UsedRange If MyCell.Hyperlinks.Count > 0 Then LinkURL = MyCell(1).Hyperlinks(1).Address FindPos = InStr(1, LinkURL, FindString) If FindPos > 0 Then 'If FindString is found ReplaceLen = Len(FindString) URLLen = Len(LinkURL) PreStr = Mid(LinkURL, 1, FindPos - 1) PostStr = Mid(LinkURL, FindPos + ReplaceLen, URLLen) NewURL = PreStr & ReplaceString & PostStr MyCell(1).Hyperlinks(1).Address = NewURL 'Change the URL End If End If Next MyCell Exit Sub ErrHandler: MsgBox ("ReplaceHyperlinkURL error") End Sub The ReplaceHyperlinkURL code must be placed in a VBA code module. From a spreadsheet, Press Alt+F11 to open the VBA Editor. Then select Insert - Module from the menu. Copy the code and paste it into the module. Then save the module. In order to run the procedure, create a macro that contains following line and run the macro in Excel. Be sure to replace the FindText with the portion of the address you want to find and ReplaceText with the text you want to replace it with. Call ReplaceHyperlinkURL("FindText", "ReplaceText") Please be sure to make a backup copy of your spreadsheet before running the macro just in case an error is made in the FindText or ReplaceText.