Remove FriendlyName from Hyperlink via Function or Macro?

Question

I know this is an odd request.

Does anyone know how to take "Click Here" type links and show the actual URL in the text instead of "Click Here." I must be able to do this to thousands of "Click Here" links at one time, manually changing it won't do at all. It appears I need a function or macro. Is this possible?

Help --- thanks!

Answer 1

Someone answered my questions on the Facebook fan page for Excel:

Sub ExtractHL()

Dim HL As Hyperlink

For Each HL In ActiveSheet.Hyperlinks

HL.Range.Offset(0, 1).Value = HL.Address

Next

End Sub

http://excel.tips.net/Pages/T003281\_Extracting\_URLs\_from\_Hyperlinks.html

Answer 2

Your code makes sense to my meager understanding of VB coding. Thanks!

Unfortunately, I'm getting an error:

"Run-time error '7':

Out of Memory"

I thought perhaps there were too many URLs so I just posted one into a new workbook and it still has the same result with the single "Click Here" link.

When I press debug it takes me to the "H.ScreenTip = ST" line of the macro... any ideas? (I have no clue why it'd be out of memory.)

Answer 3

Sub ChangeHyperlinkTips()

  Dim H As Hyperlink

  Dim ST As String

  For Each H In ActiveSheet.Hyperlinks

    ST = H.Address

    If H.SubAddress<>"" Then ST = ST & "#" & H.SubAddress

    H.ScreenTip = ST

  Next

End Sub

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk