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

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)

"J" wrote in message
...
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

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"J" wrote in message
...
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