Retrieving hyperlink from cell

[dezy]

I have hyperlinks in a spreadsheet. However, I'd like for a cell to equal
it's hyperlink address. For example, in A1 I have CCNC 2005- Consumer
Commmunications & Networking with the hyperlink address as
http://www.comsoc.org/confs/index/html. I can't see this in the formula bar.
I can only see it through the Edit Hyperlink menu option. I want D1 to equal
http://www.comsoc.org/confs/index/html and have every cell in D to follow
through. Any ideas?

Many Thanks!

 

[Gary''s Student]

The following UDF will give the Url for an Inserted Hyperlink:

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count > 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

so if A1 has a hyperlink then =hyp(A1) will display the url