Recently I needed to extract a links from a web page; normally I would use some Linux commands ported to windows and get the job done. In the end I needed to format the relative link to a full URL, so I decided to use Excel. However when I imported the web page into excel I found that the URL was embedded in the friendly name of the hyperlink.
The hyperlink command in excel actually does the reverse of what I needed; it creates a hyperlink out of the URL and a friendly name and I needed the URL from the friendly name. So I dug in a little deeper and treated the URL like an object. It has properties and those properties can be exposed. The hyperlinkcell object has a type of range so it is passed to the function of GETURL. The first hyperlink in the range we will want the property of the address. Then we simply pass it back to the function so that we can display it.
To install this all you need to do is:
- Hit ALT + F11 (Opens Visual Basic Editor)
- Click on Insert -> Module (adds a module to your excel file)
- Paste the code below for the function of GETURL
- Hit ALT + Q (Closes the Visual Basic Editor)
Now use the =GETURL(cell) to get the URL
Example: =GETURL(A1) will return the URL for the Hyperlink displayed in cell A1
Function GETURL(HyperlinkCell As Range) GETURL = HyperlinkCell.Hyperlinks(1).Address End Function