fix hyperlinks automatically

[angie]

Hi there


I have a workbook with hundreds of links to our intranet http://abc.net/corpsupportTrainingResources/Pages/TrainingandAssessment.aspx (not real address)


now the URL is changing. Instead of change each hyperlink idividually, I would like to know is there any easy ways to update the http://abc.net/corpsupportTrainingResources/Pages/TrainingandAssessment.aspx

to the new http://xyz.net/corpsupportTrainingResources/Pages/TrainingandAssessment.aspx?


Thank you very much


Regards

Angie

 

[rudders]

find and replace not an option?


Only need to swap abc.net for xyz.net? If those only occur in the hyperlinks then ctrl+f and replace them all

 

[SirJB7]

Hi, angie!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Assuming your original data is in column A from A2 in advance, type in B2 and copy down:

=HIPERVINCULO(SUSTITUIR(A2;"abc.net";"xyz.net")) -----> in english: =HYPERLINK(SUBSTITUTE(A2,"abc.net","xyz.net"))


Regards!

 

[SirJB7]

@rudders

Hi!

That will only change the displayed text not the real linking address. To check that simply click on the link after replacement, and you'll be directed to the original place.

Regards!

 

[NARAYANK991]

Hi Angie ,


SirJB7 has already posted a solution to your current problem.


However , if you are going to be doing this kind of job in future , checck out the Excel help on hyperlinks , which is reproduced below :

[pre]

Set the base address for the hyperlinks in a workbook

By default, unspecified paths to hyperlink (hyperlink: A word, phrase, picture, icon, symbol or other element in
a computer document or webpage on which a user may click to move to another part of the document or webpage or to
open another document, webpage, or file.) destination files are relative to the location of the active workbook.

Use this procedure when you want to set a different default path.

Each time that you create a hyperlink to a file in that location, you only have to specify the file name,
not the path, in the Insert Hyperlink dialog box.

1.Click the Microsoft Office Button , click Prepare, and then click Properties.

2.In the Document Information Panel, click Document Properties, and then click Advanced Properties.

3.Click the Summary tab.

4.In the Hyperlink base box, type the path that you want to use.

Note   You can override the hyperlink base address by using the full, or absolute, address for the hyperlink
in the Insert Hyperlink dialog box.

[/pre]
Narayan

 

[angie]

Dear All


Thank you for all your help and sorry I din't read the three first green sticky topics first~


SirJB7, Thanks a lot for your formula. I think I need to explain the situation in detail.


For example in A1, I've got'apple'with hyperlink to the webpage "http://abc.net/ClinicalNet/cgu/PatientSafety/Pages/default.aspx"


In A3, I've got 'banana' with hyperlink to the webpage "http://abc.net/corpsupport/workforce/Documents/ValuesandBehavioursReportFinalJuly2010.pdf"


All the workbook show the text 'apple' or' banana' or sth else other than the real webaddress.


In the future, the 'abc' will be replaced by 'xyz' but the other part of the address should be the same


which is in A1 ,it will be "http://xyz.net/ClinicalNet/cgu/PatientSafety/Pages/default.aspx" &


A3 will be "http://xyz.net/corpsupport/workforce/Documents/ValuesandBehavioursReportFinalJuly2010.pdf"


Thank you in advance


Angie

 

[SirJB7]

Hi, angie!


I now remembered that once I've done something alike, and here's the link to the file:

https://dl.dropbox.com/u/60558749/Need%20to%20change%20multiple%20hyperlinks%20to%20point%20to%20another%20folder%20%28for%20mdest%20at%20chandoo.org%29.xlsm


Do you think you can manage to handle it by yourself? It has a little piece of VBA code, very simple indeed. Just advise if any issue.


Regards!

 

[angie]

Hi SirJB7


Thank you very much. The VBA code works when I use [geturl(A1)] to extract the address (i think that's why it doesn't work initially as my cell has the friendly name instead of the address)


Thank you very much


Regards

Angie

 

[SirJB7]

Hi, angie!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!