Extracting a hyperlink

Excel does not have a function to extract the address from a cell containing a hyperlink.  Here is a short UDF that will do the job:

Function HLink(rng As Range, Optional RtnSub As Boolean = False) As String
'extract URL from hyperlink
'Orinal from http://blog.contextures.com/archives/2010/12/13/get-the-url-from-an-excel-hyperlink/
 'posted by Rick Rothstein
 'Modified by Doug Jenkins

    If rng(1).Hyperlinks.Count Then
        If RtnSub = False Then
            HLink = rng.Hyperlinks(1).Address
        Else
            HLink = rng.Hyperlinks(1).SubAddress
        End If
    End If
End Function

The original was a one-liner posted by Rick Rosthstein on Debra Dalgleish’s Contextures Blog.  I have just added an option to return the “sub-address” (part following the #), rather than the main address.  Follow the link to Contextures for more suggestions and VBA code for working with hyperlinks.

Advertisements
This entry was posted in Excel, UDFs, VBA and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s