Get the URL from an Excel Hyperlink

Last week on the Bacon Bits blog, Mike Alexander showed how to send an email with the HYPERLINK function in Excel, complete with subject line and message.

Mike’s article showed how versatile the HYPERLINK function can be, and you also learned about Mike’s unique talent for poetry.

In the steps below, I’ll show you how to get the URL from an Excel Hyperlink.

Remove Hyperlinks

If you inherit a workbook full of hyperlinks, and you want to change those, so they just contain text, you can copy the cells, and paste them as values, in another location.

If you’re using Excel 2010, you can quickly remove hyperlinks from selected cells. (Thanks to Sam for this tip.)

1. In Excel 2010, select cell(s) that contain hyperlinks
2. Right click on any selected cell
3. Click Remove hyperlinks

Macro to Remove Selected Hyperlinks

In any version, to remove hyperlinks in a group of selected cells, you can run a macro, like the one below.

Copy this code into a regular module in your workbook, then select the cells, and run the delHyperlinks macro.

Sub delHyperlinks()
  Selection.Hyperlinks.Delete
End Sub

Macro to Remove All Hyperlinks

You can also clear all the hyperlinks on the active worksheet (thanks Eric and Rick).

Sub RemoveHyperlinksOnActiveSheet()
'posted by Rick Rothstein
  Cells.Hyperlinks.Delete
End Sub

Extract Address from Hyperlink

If 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 below.

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 User Defined Function, 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

Use the HLink 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

Learn More About Hyperlinks

To learn more about adding and removing Hyperlinks, visit the Excel Hyperlinks and Hyperlink Function page on the Contextures website.

There are written instructions, and videos, like this one.

___________