Hyperlink: 3 Ways of How to Extract the Link from an Excel Cell

Sometimes, you copy webpages. Or just a link. Or you receive an Excel sheet with links in it. In such case, you often want to extract the hyperlink addresses from the cells. There are basically just three options for extracting the hyperlink address from an Excel cell. 

Method 1: Extracting the link addresses manually

The bad news first: There is no built-in way in Excel to read out a hyperlink, for example with a formula. So the first approach would be typing the hyperlink addresses manually. 

1. Right click on the cell containing the hyperlink address.
2. Click on ‘Edit Hyperlink…’.
3. Copy the link from the address field and paste it wherever you need it.

This method might work for just a couple of links. But if you got more hyperlinks to extract, you might continue with the second or third option.Also, you could try clicking on the link address. That way, a internet browser window opens and you can copy the link address from the address bar.

Method 2: Using VBA for returning the hyperlink address

As we have the bad news covered with the first option, there is also good news: With a short VBA macro it’s still possible to extract the link addresses.

The following macro defines a new excel function. You can use it by typing “=PROFEXHyperlinkAddress(A1)” into your cell (instead of A1 you can of course use any cell reference). After pressing enter, the hyperlink of cell A1 will be displayed. If there is no hyperlink in a cell, nothing will be shown.

Just start by opening the VBA window (Ribbon “Developer”–>”Editor”). Then add a new module (right click in the Project Explorer –> Insert –> Module) and paste the following code into the new macro. If you need more help with VBA macros, please refer to this article.

Function PROFEXHyperlinkAddress(Zelle As Range)
'This function returns a hyperlink address from a cell, for example from a text, copied from the internet

'The cell with the actual hyperlink will be saved in "Zelle"

Dim Link As String
'The link will be saved in the variable "Link"

Application.Volatile
'With "Application.Volatile" you can make sure, that the function will be recalculated once the worksheet is recalculated
'for example, when you press F9 (Windows) or press enter in a cell

If Zelle.Hyperlinks.count Then
'Return the address if there is at least one cell selected
Link = Zelle.Hyperlinks(1).Address

'Return the link address of the first item of the array (in case there are more than one cell selected)

End If

PROFEXHyperlinkAddress = Link
'Return the link

End Function

Method 3: Inserting the link address with ‘Professor Excel Tools’

The third option doesn’t involve any VBA. Just download the Excel add-in Professor Excel Tools from below (no sign-up, just activate it within Excel). For getting the link address type =PROFEXHyperlinkAddress(A1) into a cell. Instead of A1 you refer to the cell containing the link.