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