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.
1. Select the range which contanis 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:
With the Kutools for Excel’s Convert Hyperlinks feature, you can quickly extract the real addresses from the hyperlinks.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
After installing Kutools for Excel, please do as follows:( Free Download Kutools for Excel Now )
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.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 5 years agoseems you forgot to tell folks to select a range. if I select a range of cells it creates the list, otherwise I get an empty list like yael did.
- To post as a guest, your comment is unpublished.· 5 years agoThis was a life saver. For anyone looking to have the hyperlink display where documents are stored on a network path, this should work for you. I didn't select anything, followed the steps, and it created a new sheet with the links expanded which I was able to turn into plain text and use as raw data.
- To post as a guest, your comment is unpublished.· 5 years agoHi,
It seems that the address selection applies to only web pages and not to other places in the document.
- To post as a guest, your comment is unpublished.· 6 years agoThis is awesome!
Is there a way to show the full path?
It seems to be truncating the file path a little.
- To post as a guest, your comment is unpublished.· 6 years agothis is a treasure!!!
- To post as a guest, your comment is unpublished.· 6 years agoIt does not work. Just empty page created