Spreadsheet 123 Excel 2003, XP, 2000, 97 FAQs: Extract Hyperlink

Question:  I've downloaded a table from the web with hyperlink references into 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)