Extract HyperLinks in Excel

It's been a while since my last post - I know. :))

Here's a quick macros and function I find very helpful and would like to share with you.

Copy and paste the following macros function for your sheet 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


After that, you can easily extract by keying in a new function. Let's say column A is where your Hyper Links are:

=HyperLinkText(A1)

Copy and paste the above new function in column B and viola! :)

Hope this helps!