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

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