Excel Guides

Extracting Hyperlink Information in Excel

There are a few ways to extract hyperlink information in Excel. One way is to use the HYPERLINK function. This function will return the display text, URL, and SubAddress for a given cell that contains a hyperlink. Another way is to use the Hyperlinks dialog box. This dialog box can be opened by going to Insert > Hyperlink or by pressing Ctrl+K. In the dialog box, you can view and edit the properties of existing hyperlinks, as well as add new ones.

If you want to extract hyperlink information from a large number of cells, you can use a macro. The following macro will loop through all cells in the active sheet and extract the hyperlink information into three columns:

Sub ExtractHyperlinks()
 Dim i As Long
 Dim j As Long
 Dim k As Long
 Dim LastRow As Long
 Dim ws As Worksheet

 Set ws = ActiveSheet

 'Find the last row with data in column A
 LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

 'Loop through each cell in column A and extract hyperlink info
 For i = 1 To LastRow
  For j = 1 To 3
   k = k + 1

   Select Case j
    Case 1 'Display Text
     Cells(k, 1) = ws.Cells(i, 1).Hyperlinks(1).TextToDisplay

    Case 2 'URL Address
     Cells(k, 2) = ws.Cells(i, 1).Hyperlinks(1).Address

    Case 3 'SubAddress (optional)
     Cells(k, 3) = ws.Cells(i, 1).Hyperlinks(1).SubAddress

   End Select

  Next j

 Next i

 End Sub

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.