Search & Replace in Hyperlink field

[Guest]

Is it possible to perform a Search&Replace in a Hyperlink field?

I've tried, but cannot get it to work ... ??

 

[Dave Peterson]

You could have a little macro do it:

From David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
(look for Fix Hyperlinks)

Ah, it's pretty small:

Option Explicit
Sub Fix192Hyperlinks()
Dim OldStr As String, NewStr As String
OldStr = "http://192.168.15.5/"
NewStr = "http://hank.home.on.ca/"
Dim hyp As Hyperlink
For Each hyp In ActiveSheet.Hyperlinks
hyp.Address = Replace(hyp.Address, OldStr, NewStr)
Next hyp
End Sub

If you're using xl97, then change this:
hyp.Address = Replace(hyp.Address, OldStr, NewStr)
to
hyp.Address = application.substitute(hyp.Address, OldStr, NewStr)

(replace was added in xl2k)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm