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.