Mass Change of Text to Display in Hyperlink to actual address

ItsChris · #1 10-17-2014, 09:19 AM

Hi, I've inherited an inventory list that has a field (column) with a hyperlink to the online item. The Text to Display section is standard for all items "View Vehicle" with a unique Address URL for each item. I would like to have the actual URL address show vs "View Vehicle"

Without going item by item for hundreds of lines, is there a way to mass update?

Reason I need to show the URL address is I need to update components of the address - basically a find and replace action.

Using Excel 2013

Thank you for your help.
Chris

macropod · #2 10-17-2014, 10:26 PM

Changing the display text for all hyperlinks requires a macro:

Code:

Sub Demo()
Dim hLnk As Hyperlink
For Each hLnk In ActiveSheet.Hyperlinks
    hLnk.TextToDisplay = hLnk.Address
Next
End Sub

ItsChris · #3 10-18-2014, 10:46 AM

thank you - very much appreciated.

Any hints for a macro that will automatically do find and replace actions throughout worksheet?

ie: Find domain.com and replace with xyzaddress.ca

again, thank you for your help.

macropod · #4 10-18-2014, 01:35 PM

It would have been helpful had you said you wanted to do that. Assuming these domain names are only in the hyperlinks, try:

Code:

Sub Demo()
Dim hLnk As Hyperlink
For Each hLnk In ActiveSheet.Hyperlinks
  With hLnk
    .Address = Replace(.Address, "domain.com", "xyzaddress.ca")
    .TextToDisplay = .Address
  End With
Next
End Sub

For anything else, you could use Find/Replace

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Change Text To Hyperlink	abraxis	Outlook	0	10-09-2014 08:24 AM
Add link to email address that hides the actual address or makes it inaccessible to online bots	richiebabes	Word	1	09-03-2014 03:22 PM
Delete specific mass text on document	JadeRisley	Word	4	07-17-2013 11:11 PM
How do you mass change "Text to Display" on hyperlinks	lvillanueva5	Mail Merge	1	09-10-2012 05:32 PM
Can't change period when adjusting actual time in Web Access	chip_saffire	Project	0	10-04-2011 12:05 AM