Skip to content Skip to navigation

OpenStax-CNX

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

Navigation

Recently Viewed

This feature requires Javascript to be enabled.
 

Hyperlink Search and Replace in Microsoft Excel

Module by: Trent Kelly. E-mail the author

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 As String
Dim PreStr As String
Dim PostStr As String
Dim NewURL As String
Dim FindPos As Integer
Dim ReplaceLen As Integer
Dim 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

Public Sub WBReplaceHyperlinkURL(FindString As String, ReplaceString As String)
'For all sheets in the workbook

Dim LinkURL As String
Dim PreStr As String
Dim PostStr As String
Dim NewURL As String
Dim FindPos As Integer
Dim ReplaceLen As Integer
Dim URLLen As Integer
Dim MyDoc As Worksheet
Dim MyCell As Range
On Error GoTo ErrHandler

For Each WS In Worksheets
 WS.Activate
 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
Next WS

MsgBox ("Hyperlink Replacement Complete")
Exit Sub
ErrHandler:
MsgBox ("ReplaceHyperlinkURL error")
End Sub

The code must be placed in a VBA code module. From a spreadsheet, open the VBA Editor in the developer ribbon. The developer ribbon can be turned on in the popular tab of Excel Options. 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. If you want to perform the search and replace on all sheets in the workbook, use the WBReplaceHyperlinkURL routine rather than ReplaceHyperlinkURL.

Content actions

Download module as:

Add module to:

My Favorites (?)

'My Favorites' is a special kind of lens which you can use to bookmark modules and collections. 'My Favorites' can only be seen by you, and collections saved in 'My Favorites' can remember the last module you were on. You need an account to use 'My Favorites'.

| A lens I own (?)

Definition of a lens

Lenses

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

What is in a lens?

Lens makers point to 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 member, a community, or a respected organization.

What are tags? tag icon

Tags are descriptors added by lens makers to help label content, attaching a vocabulary that is meaningful in the context of the lens.

| External bookmarks