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.