change part of hyperlink formula

[Big Mac]

I have a spreadsheet with several hyperlinks and would lile to be able to
search each hyperlink in turn and replace part of the text with another, a
sort of edit replace, (which doesn't edit the hyperlink formula)
I would like to replace s:\ with x:\it\
Ideally I would like a popup box to ask for the replace and then the with.
This would help with several other files, since we changed our folder
structure.

Also some were put in with insert hyperlink and some were put in with insert
formula hyperlink

Any advice would be extremely appreciated

Thanks

 

[Gary''s Student]

Find/replace should work just fine for =HYPERLINK() formulas. Starting with:

=HYPERLINK("file:///s:\Documents and
Settings\User\Desktop\Book2.xls#Sheet2!B9")


and replacing:
s:\
with:
x:\it\

results in:

=HYPERLINK("file:///x:\it\Documents and
Settings\User\Desktop\Book2.xls#Sheet2!B9")

 

[Big Mac]

Thanks but I haven't used the hyperlink formula. instead my colleagues have
used the hyperlink button on the toolbar. This creates a hyperlink like in
Word.

To change this you have to use the Edit Hyperlink process. When you use edit
and replace it only changes the visible part of the text "Text to display"
and doesn't change the underlying hyperlink but I want to be able to search
the document for this type of hyperlink and then change the path as there are
thousands of entries like this

Ay Ideas?

 

[Dave Peterson]

You could use the code from David McRitchie's site:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm
look for:
Fix Hyperlinks (#FixHyperlinks)

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