Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Extract hyperlink string from excel cell

111 views
Skip to first unread message

Markus Riester

unread,
Jul 26, 2002, 12:09:25 PM7/26/02
to
Hello,
does anyone know how I can extract the hypertext string
from an Excel cell. Not the text that is displayed but the
actual link string?

Is there a function that is available (I didn't find one
in the standard functions Excel offers), or does anyone
have a macro?

Thanks in advance,

Markus

David McRitchie

unread,
Jul 26, 2002, 4:04:13 PM7/26/02
to
Hi Markus,

Function HyperlinkAddress(cell)
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function

=HyperlinkAddress(A1)

More infomation on this and similar hyperlink things in
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Markus Riester" <Markus....@motorola.com> wrote in message news:186501c234be$cfaf06a0$36ef2ecf@tkmsftngxa12...

Bill Manville

unread,
Jul 26, 2002, 6:21:50 PM7/26/02
to
Markus Riester wrote:
> Is there a function that is available (I didn't find one
> in the standard functions Excel offers), or does anyone
> have a macro?
>
Something like this?

Function HyperLinkText(oRange As Range) As String
Dim ST1 As String, ST2 As String
If oRange.Hyperlinks.Count = 0 Then Exit Function
ST1 = oRange.Hyperlinks(1).Address
ST2 = oRange.Hyperlinks(1).SubAddress
If ST2 <> "" Then ST1 = "[" & ST1 & "]" & ST2
HyperLinkText = ST1
End Function


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Ryan Sapien

unread,
Jan 19, 2005, 2:46:14 PM1/19/05
to
Forgive my ignorance but I haven't done a lot of work with Excel VBA.
So do I just have to paste the function into the VBA window and then
add =HyperLinkText(CellName) on my worksheet cell?

Thanks

Ryan

Bill Manville

unread,
Jan 19, 2005, 7:24:06 PM1/19/05
to
Ryan Sapien wrote:
> So do I just have to paste the function into the VBA window and then
> add =HyperLinkText(CellName) on my worksheet cell?
>

Yes
In the VB editor you need to Insert / Module if there are no standard
modules already in your workbook. Then paste the code into that
module.

0 new messages