# Connexions

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

### 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, 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 FindPos > 0 Then 'If FindString is found
ReplaceLen = Len(FindString)
PreStr = Mid(LinkURL, 1, FindPos - 1)
PostStr = Mid(LinkURL, FindPos + ReplaceLen, URLLen)
NewURL = PreStr & ReplaceString & PostStr
End If
End If
Next MyCell
Exit Sub
ErrHandler:
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.

## Content actions

PDF | EPUB (?)

### What is an EPUB file?

EPUB is an electronic book format that can be read on a variety of mobile devices.

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?

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