This has personally worked like a charm for me.
Try this user-defined function:
Where A1 is the cell with the hyperlink in it. Here is the Macro function code:
Public Function ShowAddress(rng As Range) As String
If rng.Cells.Count > 1 Then
ShowAddress = CVErr(xlErrValue)
ShowAddress = rng.Hyperlinks.Item(1).Address
Note: With Excel 2007 and 2010 you first need to save a document as “.xlsm” to enable macros!