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

Share this

Author : itkampekkampok.blogspot.com

Related Posts