Cheryl has a worksheet that contains many hyperlinks. The display text for each hyperlink is different than the target for the hyperlink. These hyperlinks are all in column A. Cheryl would like to leave the display text in column A, move the target URL into column B, and delete the hyperlink in column A. What she needs to end up with is the display text in column A, the URL in column B, and no active hyperlinks in the worksheet.
Processing and extracting information from hyperlinks in this manner requires the use of a macro. The following is an example of a flexible macro that examines whatever hyperlinks are in the selected range of cells. If a hyperlink is found, the URL for the hyperlink is entered to the right of the hyperlink and then the hyperlink itself is deleted. This leaves the display text in the cell where the hyperlink used to be.
Sub GetHLInfo() Dim rRng As Range Dim cell As Range Set rRng = ActiveSheet.Range(ActiveWindow.Selection.Address) For Each cell In rRng If cell.Hyperlinks.Count > 0 Then cell.Offset(0, 1) = cell.Hyperlinks(1).Address cell.Hyperlinks(1).Delete End If Next End Sub