Skip to content Skip to navigation

Connexions

You are here: Home » Content » Hyperlink Search and Replace in Microsoft Excel

Navigation

Content Actions
  • Download module PDF
  • Add to ...
    Add the module to:
    • My Favorites
    • A lens
    • An external social bookmarking service
    • My Favorites (What is 'My Favorites'?)
      'My Favorites' is a special kind of lens which you can use to bookmark modules and collections directly in Connexions. 'My Favorites' can only be seen by you, and collections saved in 'My Favorites' can remember the last module you were on. You need a Connexions account to use 'My Favorites'.
    • A lens (What is a lens?)

      Definition of a lens

      Lenses

      A lens is a custom view of Connexions content. You can think of it as a fancy kind of list that will let you see Connexions through the eyes of organizations and people you trust.

      What is in a lens?

      Lens makers point to Connexions materials (modules and collections), creating a guide that includes their own comments and descriptive tags about the content.

      Who can create a lens?

      Any individual Connexions member, a community, or a respected organization.

    • External bookmarks
  • E-mail the author

Recently Viewed

This feature requires Javascript to be enabled.

Hyperlink Search and Replace in Microsoft Excel

Module by: Trent Kelly

Summary: 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.

Comments, questions, feedback, criticisms?

Send feedback