How do I extract a hyperlink?

[al...@micronet.dk]

I have have an Excel worksheet with hyperlinks and I want to extract
the hyperlink (in this case a Internet URL) but I cannot find a
function that does it. It only takes the hyperlink text and not the
hyperlink itself. There are thousands of hyperlink so I will NOT copy
and paste every single link!! Does anyone have a solution?

-Allan

Sent via Deja.com http://www.deja.com/
Before you buy.

[George Nicholson]

Allan:
See if this helps, although its not a function.
Just change the Sheet1 reference to whatever you need it
to be and change the Sheet2 reference to point to a blank sheet.
*********************
Sub ListHyperlinks()
' For all hyperlinks on Sheet 1, this will a create list on Sheet 2
' that displays the cell address of the hyperlink in Column A
' the cell value (i.e., the visible text) in Column B
' and the hyperlink reference in Column C.
Dim h As hyperlink
Dim x As Integer

x = 1
For Each h In Worksheets("Sheet1").Hyperlinks
Worksheets("Sheet2").Range("A" & x).Value = h.Range.Address
Worksheets("Sheet2").Range("B" & x).Value = h.Range.Value
Worksheets("Sheet2").Range("C" & x).Value = h.Name
x = x + 1
Next
End Sub
************************
HTH,
GeorgeN

<al...@micronet.dk> wrote in message news:8cadaq$5q9$1...@nnrp1.deja.com...

[David McRitchie]

Hi Allan,
I think I answered you this morning in Email where I actually
listed the macros, anyway for everyone's benefit there are two
functions in http://members.aol.com/rexx03/buildtoc.htm
one is URL and the other is HyperlinkAddress.
=URL(a14)
=HyperLinkAddress(a14)

There is nothing builtin to Excel you have to use a UDF
(User Defined Function) or a Subroutine such as George
Nicholson gave you in the newsgroup.

Instructions to install a function or a macro can be found
on my formula page. (you can install either in a regular
module). http://members.aol.com/dmcritchie/formula.htm

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm