How to quickly list all hyperlinks in Excel?

When we use a worksheet, maybe we create many hyperlinks in it, and sometimes we need to know where the texts link to. If you click them one by one, it will be tedious and time-consuming. If we can list all hyperlinks with texts and hyperlinks addresses in a worksheet, it will be easy for us to check all the hyperlinks in Excel.

List all selected hyperlinks with VBA code

Extract all actual addresses from hyperlinks with Kutools for Excel

List all selected hyperlinks with VBA code

1. Select the range which contains the hyperlinks that you want to list.

2. Click Developer >Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module:

Option Explicit
Private Function GetHyperAddy(Cell As Range) As String
On Error Resume Next
GetHyperAddy = Cell.Hyperlinks.Item(1).Address
If Err.Number <> 0 Then GetHyperAddy = "None"
On Error GoTo 0
End Function
Sub DistillHyperlinks()
Dim HyperAddy As String, cl As Range, wsTarget As Worksheet, clSource As Range
Application.ScreenUpdating = False
Set clSource = Selection
On Error Resume Next
Set wsTarget = Sheets("Hyperlink List")
If Err.Number <> 0 Then 
Set wsTarget = Worksheets.Add
With wsTarget
.Name = "Hyperlink List"
With .Range("A1")
.Value = "Location"
.ColumnWidth = 20
.Font.Bold = True
End With
With .Range("B1")
.Value = "Displayed Text"
.ColumnWidth = 25
.Font.Bold = True
End With
With .Range("C1")
.Value = "Hyperlink Target"
.ColumnWidth = 40
.Font.Bold = True
End With
End With
Set wsTarget = Sheets("Hyperlink List")
End If
On Error GoTo 0
For Each cl In clSource
HyperAddy = GetHyperAddy(cl)
If Not HyperAddy = "None" Then
With wsTarget.Range("A65536").End(xlUp).Offset(1, 0)
.Parent.Hyperlinks.Add Anchor:=.Offset(0, 0), _
Address:="", SubAddress:=(cl.Parent.Name) & "!" & (cl.Address)
.Offset(0, 1).Value = cl.Text
.Hyperlinks.Add Anchor:=.Offset(0, 2), Address:=HyperAddy
End With
End If
Next cl
wsTarget.Select
End Sub

3. Then click button to run the code. And all of the hyperlinks will be displayed in a new worksheet called Hyperlink List. See screenshot:

Extract all actual addresses from hyperlinks with Kutools for Excel

With the Kutools for Excel’s Convert Hyperlinks feature, you can quickly extract the actual addresses from the hyperlinks.

1. Select the hyperlinks that you want to extract the real destinations.

2. Then click Kutools > Link > Convert Hyperlinks, see screenshot:

3. In the Convert Hyperlinks dialog box, select Hyperlinks addresses replace cell contents option from the Convert type section, and then specify a cell where you want to put the result under the Result range section, see screenshot:

4. Then click OK button, all the real addresses have been extracted from the hyperlinks, see screenshot:

Note: Convert source range: the extracted results will be located on the original cell range if you check this option.

Related Article:

How to quickly delete all hyperlinks in Excel?