Get the URL from an Excel Hyperlink

hyperlinkdragIf you paste hyperlinks as values, what you're left with is the "Friendly Name." That's the text you can see in the cell with the hyperlink, like "Sales Report" in the screen shot on left.

Instead of the Friendly Name, you might want to extract the hyperlink's location. There's no built-in function for that in Excel, but you can create your own, with a bit of VBA.

Copy the HLink code, shown below, into a regular module in your workbook.

Function HLink(rng As Range) As String
'extract URL from hyperlink
'posted by Rick Rothstein
  If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
End Function

Then, you can use the HLink function in that workbook, just like any other Excel function. For example, to find the address for a hyperlink in cell B3, use this formula: =HLink(B3)

To extract the Friendly Name, use a simple link to the cell: =B3