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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!