Mass Hyperlink Edits?

[dstarns]

We have a workbook that has many worksheets. Each worksheet has many hyperlinks pointing to files on our network drive.

The problem is the hyperlinks all point to the exact path:

\\server1\excel\books\*.*

Is there a way to edit all the hyperlinks in the workbook and doing a find/replace like this

find: \\server1\excel\books\
replace: \\newserver\newlocation\

Any help or advice would be great. I love how this community works together.

Dave

 

[RomulusMilea]

Hi Dave,

Were all those hyperlinks entered by using HYPERLINK function (preferably) ? Or, were they introduced by using CTRL + K command (I hope not) ?

 

[dstarns]

After a little investigation it looks like they were all put in using the CTRL+K option, by the "(I hope not)" I'm worried there isn't an easy fix...

 

[RomulusMilea]

Hello again,

First of all, from now on, please make sure any new hyperlink will be created by using HYPERLINK function. Exemple:

=HYPERLINK("\\NewServer\NewLocation","Text to display").

There are two parameters, separated with a comma "," or a semicolon ";" depending on your Regional settings. I always prefer comma ",".

Now, to have your problem solved, you need to convert all your hyperlinks from CTRL + K version to HYPERLINK function version. If you did this, you just need a "classic" CTRL + H, i.e. replace.

To convert the hyperlinks, I would suggest to download, install and use "ASAP Utilities", see www.asap-utilities.com, free version would be just fine.

You just have to chosse "Web" menu, then "Change all hyperlinks to HYPERLINK() formulas".

Cheers, and all the best.