Today, I faced the problem of extracting URLs from a long list of hyperlinked text. I had two options:
First is to do it manually:
- Right-click a hyperlink.
- From the Context menu, choose Edit Hyperlink. Excel displays the Edit Hyperlink dialog box.
- Select and copy (Ctrl+C) the entire URL from the Address field of the dialog box.
- Press Esc to close the Edit Hyperlink dialog box.
- Paste the URL into any cell desired.
But then I have a long list of hyperlinks in my Excel worksheet. If I had to do this for each and every single hyperlink, this can get tedious very very quickly. So the second option is to get the URLs using a macro.
The following example can be useful when extracting hyperlinks from tables of links that have been copied into Excel, when doing post-processing on Excel web queries, or getting the email address from a list of “mailto:” hyperlinks.
Extracting a URL from a hyperlink on Excel is easy!
- Open up a new workbook.
- Get into VBA (Press Alt+F11)
- Insert a new module (Insert > Module)
- Copy and Paste the Excel user defined function below
- Get out of VBA (Press Alt+Q)
- Use this syntax for this custom Excel function: =GetURL(cell,[default_value])
Function GetURL(cell As range, _
Optional default_value As Variant)
'Lists the Hyperlink Address for a Given Cell
'If cell does not contain a hyperlink, return default_value
If (cell.range("A1").Hyperlinks.Count <> 1) Then
GetURL = default_value
GetURL = cell.range("A1").Hyperlinks(1).Address