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:

1. Right-click a hyperlink.
2. From the Context menu, choose Edit Hyperlink. Excel displays the Edit Hyperlink dialog box.
3. Select and copy (Ctrl+C) the entire URL from the Address field of the dialog box.
4. Press Esc to close the Edit Hyperlink dialog box.
5. 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

1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function below
5. Press F5 and click “Run”
6. 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

1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function below
5. Get out of VBA (Press Alt+Q)
6. 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