function about hyperlink

[Poon Xiulok]

Is there a worksheet function that returns the contents of a hyperlink? For
instance, the content of cell A1 is "something" which contains a hyperlink
to "somewhere". How can I show "somewhere" in B1?

 

[Dave Peterson]

One way to extract those URL's from a hyperlink created via Insert|Hyperlink
is with a userdefinedfunction.

Here's one that may help:

Option Explicit
Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If
End Function

So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent
cell.

Be aware that if you change the hyperlink, then this formula cell won't change
until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=getURL(a1)

 

[Gord Dibben]

Dave

This UDF returns nothing when hyperlink is to a second sheet in same workbook.

Any way to alter?

Gord Dibben Excel MVP

 

[David McRitchie]

The GetURL User Defined Formula is for a webpage URL
For a worksheet see Bill Manville's solution
in http://www.mvps.org/dmcritchie/excel/buildtoc.htm
that he posted 2002-07-26, covers Excel links as well,
and is only for object type hyperlinks. But you can use it
for webpage URL or for references to cells.

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

The result will look like this:
[]Sheet2!D3
which is really [ ]Sheet2!D3 and not showing the
workbookname.

 

[Gord Dibben]

David

getURL reurns a result when hyperlinked to another Workbook, just not a sheet
in same workbook.

Thanks for the Bill Manville code reference.

Gord

The GetURL User Defined Formula is for a webpage URL
For a worksheet see Bill Manville's solution
in http://www.mvps.org/dmcritchie/excel/buildtoc.htm
that he posted 2002-07-26, covers Excel links as well,
and is only for object type hyperlinks. But you can use it
for webpage URL or for references to cells.

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

The result will look like this:
[]Sheet2!D3
which is really [ ]Sheet2!D3 and not showing the
workbookname.

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

Dave

This UDF returns nothing when hyperlink is to a second sheet in same workbook.

Any way to alter?

Gord Dibben Excel MVP