Extract a Hyperlink from a Cell in an Excel Spreadsheet

Sometimes you might copy a group of links from the internet and want to get the underlying URL to save in a database or something. In order to get Excel to give up the URL you’ll have to use a Macro.

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)