---

MS Excel 2003: Extract hyperlink address (web addresses ONLY)

This Excel tutorial explains how to write a macro to extract web hyperlink addresses in Excel 2003 and older versions (with screenshots and step-by-step instructions).

Question: I've downloaded a table from the web with hyperlink references into Microsoft Excel 2003/XP/2000/97. What I need to do is examine the hypertext link and extract a small part of that link. All I seem to get is the details of the link in a pop up box (yellow) and any attempt to filter out detail only results in the display of the destination ?

Is there a way I can examine a hyperlink details without invoking the hyperlink?

Answer: Below is a function that you can include in your spreadsheet to extract the hyperlink address from a cell in Excel.

Function HyperLinkText(pRange As Range) As String

   Dim ST1 As String
   Dim ST2 As String

   If pRange.Hyperlinks.Count = 0 Then
      Exit Function
   End If

   ST1 = pRange.Hyperlinks(1).Address
   ST2 = pRange.Hyperlinks(1).SubAddress

   If ST2 <> "" Then
      ST1 = "[" & ST1 & "]" & ST2
   End If

   HyperLinkText = ST1

End Function

Then you can reference this new function in your spreadsheet.

For example in cell B1, you could enter the following:

=HyperLinkText(A1)