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.
- Right click on the cell containing the hyperlink address.
- Click on ‘Edit Hyperlink…’.
- 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.
Have you ever built an app based on Excel?
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.
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
With Method 2: Using VBA for returning the hyperlink address, the PROFEXHyperlinkAddress function worked LIKE A CHARM with Excel 2007. Thanks.
Thanks for the feedback, glad that it worked! 🙂
Thanks so much for posting that code/snippet, Henrik. Obviously … saves digging through ref manuals. Cheers!