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
ST1 = pRange.Hyperlinks(1).Address
ST2 = pRange.Hyperlinks(1).SubAddress
If ST2 <> “” Then
ST1 = “[” & ST1 & “]” & ST2
HyperLinkText = ST1
Then you can reference this new function in your spreadsheet.
For example in cell B1, you could enter the following: