To get the hyperlink of a cell you can use a simple UDF, like this:
Function RETURNHYPERLINK(Rng As Range) As String
If Rng.Hyperlinks.Count > 0 Then
RETURNHYPERLINK = Rng.Hyperlinks(1).Address
If Len(RETURNHYPERLINK) = 0 Then
RETURNHYPERLINK = Rng.Hyperlinks(1).SubAddress
End If
End If
End Function
If Rng.Hyperlinks.Count > 0 Then
RETURNHYPERLINK = Rng.Hyperlinks(1).Address
If Len(RETURNHYPERLINK) = 0 Then
RETURNHYPERLINK = Rng.Hyperlinks(1).SubAddress
End If
End If
End Function
You can then use it in your worksheet like
=RETURNHYPERLINK(A13)
I check for the Address and the SubAddress properties because a hyperlink to a “place in this document” for example is not stored in the Address property (which appears empty), but in the SubAddress.
Hi There
The utility seems usefull but maybe lacks information from your side or experience from my side.
I copied the “Code” to the Macroeditor, but in Excel putting =RETURNHYPERLINK(D7) returns with #Name
I did put a friendly_name with a hyperlink in that cel!
Thanks Anyway,
zab
Zabzoo,
The code needs to go in a standard module, in the workbook where you want to use the formula. Look at this for more info:
http://www.danielklann.com/excel/where_do_i_place_code_in_excel.htm
http://www.cpearson.com/excel/codemods.htm
I’ve just spent some thirty minutes to uncover that the fragment part of URL (the one after hash # symbol) is available through the Hyperlinks(1).SubAddress . I hope that helps.