Return the hyperlink

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

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.

Posted in Uncategorized

3 thoughts on “Return the hyperlink

  1. 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

  2. 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.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *