need a function or property to extract the hyperlink from a cell

[J]

I copy a list of hyperlinks with friendly names into a spreadsheet.
However, I want to extract the underlying hyperlinks into their own cells.
Is there a function or a property I can use in a macro to address the
hyperlink behind the friendly name?

Thx,

J

 

[Ron Coderre]

Perhaps this User Defined Function:

Function GetHyperlink(rngCell As Range)
GetHyperlink = rngCell.Hyperlinks(1).Address
End Function

Then....
If Cell A1 contains the hyperlink,

This formula returns the web address of that hyperlink:
=GetHyperlink(A1)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Bernard Liengme]

In A1 I entered this formula: =HYPERLINK("www.abc.ca","click for abc")
In B1 I use =GETLINK(A1) and this returned: www.abc.ca
The UDF is
Function getlink(mycell)
myform = mycell.Formula
If Mid(myform, 2, 9) = "HYPERLINK" Then
commapos = InStr(1, myform, ",")
getlink = Mid(myform, 13, commapos - 14)
Else
getlink = ""
End If
End Function

best wishes