I have a column of hyperlinks that have different text to display than the address itself. I want to be able to extract the cell ref for each cell that has a hyperlink and the place hyperlink text in the adjacent cell.

Anyone know how to do this?

TIA
Mark

 

Got this from another forum

Create the following custom function in a standard module:

Function HyperlinkAddress(oCell As Range) As String
On Error Resume Next
HyperlinkAddress = oCell.Hyperlinks(1).Address
End Function

(The line On Error Resume Next avoids error values if the cell referred to does not contain a hyperlink.)

Say that A1 contains a hyperlink. Enter the following formula in B1 to see the hyperlink address:

=HyperlinkAddress(A1)

If you store the function in a module in your Personal.xls, use

=Personal.xls!HyperlinkAddress(A1)