How to extract a URL from a hyperlink on Excel
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!
Option 1: If you want to run this operation one time
- 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
- Press F5 and click “Run”
- Get out of VBA (Press Alt+Q)
Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub
Option 2: If you plan to add more hyperlinks to the spreadsheet and need to store the formula on the sheet
- 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
Else
GetURL = cell.range("A1").Hyperlinks(1).Address & "#" & cell.range("A1").Hyperlinks(1).SubAddress
End If
End Function
Looking to extract a URL from a hyperlinked image, graphic, or icon? View steps to extract a URL from a hyperlinked image, graphic, or icon »
منبع: https://howtouseexcel.net/how-to-extract-a-url-from-a-hyperlink-on-excel