How do I retrieve the url portion of a cell that has a hyperlink stored int
it. The value function only returns the readable text.

If there is a hyperlink in C5, then the UDF
=hyp("C5") will return the URL


Here is the UDF:

Function hyp(r As String) As String
hyp = Range(r).Hyperlinks(1).Address
End Function
--
Gary's Student


"polytx" wrote:

How do I retrieve the url portion of a cell that has a hyperlink stored int
it. The value function only returns the readable text.

Hi Polb and Gary's,

If there is no hyperlink I think you would get zero

try

Function HyperlinkAddress(cell) As String
If cell.Hyperlinks.Count 0 Then _
HyperlinkAddress = cell.Hyperlinks(1).Address
End Function

to use:
=personal.xls!hyperlinkaddress(A1)

more informiation an variations see
http://www.mvps.org/dmcritchie/excel...perlinkaddress

---
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

"Gary''s Student" wrote in message
...
If there is a hyperlink in C5, then the UDF
=hyp("C5") will return the URL


Here is the UDF:

Function hyp(r As String) As String
hyp = Range(r).Hyperlinks(1).Address
End Function
--
Gary's Student


"polytx" wrote:

How do I retrieve the url portion of a cell that has a hyperlink stored int
it. The value function only returns the readable text.

I feel like a dork, but I'm not familiar with how to create a UDF or how to
work with personal.xls. Is there a primer that you can direct me to that can
help?

I have a 2,000 row spreadsheet where I need to extract the URL an embedded
column, and can't stand the thought of doing that by hand. ;o)

Thanks in advance for baby-sitting me on this!

"David McRitchie" wrote:

Hi Polb and Gary's,

If there is no hyperlink I think you would get zero

try

Function HyperlinkAddress(cell) As String
If cell.Hyperlinks.Count 0 Then _
HyperlinkAddress = cell.Hyperlinks(1).Address
End Function

to use:
=personal.xls!hyperlinkaddress(A1)

more informiation an variations see
http://www.mvps.org/dmcritchie/excel...perlinkaddress

---
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

"Gary''s Student" wrote in message
...
If there is a hyperlink in C5, then the UDF
=hyp("C5") will return the URL


Here is the UDF:

Function hyp(r As String) As String
hyp = Range(r).Hyperlinks(1).Address
End Function
--
Gary's Student


"polytx" wrote:

How do I retrieve the url portion of a cell that has a hyperlink stored int
it. The value function only returns the readable text.

There is a reference on the referred to page that would point you to
http://www.mvps.org/dmcritchie/excel....htm#havemacro
this reference is more specific as you have the User Defined Function
and just want to install it. Once you've installed a UDF or a macro
it will go a lot easier the second time.
---
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

"FrozenRope" wrote in message ...
I feel like a dork, but I'm not familiar with how to create a UDF or how to
work with personal.xls. Is there a primer that you can direct me to that can
help?

I have a 2,000 row spreadsheet where I need to extract the URL an embedded
column, and can't stand the thought of doing that by hand. ;o)

Thanks in advance for baby-sitting me on this!

"David McRitchie" wrote:

Hi Polb and Gary's,

If there is no hyperlink I think you would get zero

try

Function HyperlinkAddress(cell) As String
If cell.Hyperlinks.Count 0 Then _
HyperlinkAddress = cell.Hyperlinks(1).Address
End Function

to use:
=personal.xls!hyperlinkaddress(A1)

more informiation an variations see
http://www.mvps.org/dmcritchie/excel...perlinkaddress

---
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

"Gary''s Student" wrote in message
...
If there is a hyperlink in C5, then the UDF
=hyp("C5") will return the URL


Here is the UDF:

Function hyp(r As String) As String
hyp = Range(r).Hyperlinks(1).Address
End Function
--
Gary's Student


"polytx" wrote:

How do I retrieve the url portion of a cell that has a hyperlink stored int
it. The value function only returns the readable text.

Hi FrozenRope,

Here's a UDF to extract the Hyperlink from a cell, plus a macro that uses
the same UDF to test whether a file referenced by a hyperlink exists.

You can use the UDF just like a formula. The syntax is:
=HLinkAddr(A1)
where A1 is the cell you want to extract the link from. The UDF returns the
Hyperlink in the form "C:\My Documents\MyFile.ext FileRef" where 'FileRef'
is a bookmark or a worksheet & cell reference.

Dim Source As Range
Dim Called As Boolean
Dim HAddr As String
Dim HSubAddr As String

Public Function HLinkAddr(Source As Range)
If Called = True And Source.Hyperlinks.Count = 0 Then Exit Function
HAddr = Replace(Source.Hyperlinks(1).Address, "/", "\")
If Trim(HAddr) = "" Then HAddr = ThisWorkbook.FullName
If InStr(HAddr, "..\") Then HAddr = ThisWorkbook.Path & Replace(HAddr,
"..\", "")
If InStr(HAddr, ":") = False Then HAddr = ThisWorkbook.Path & "\" & HAddr
HSubAddr = Source.Hyperlinks(1).SubAddress
If HSubAddr = "" Then
HLinkAddr = HAddr
Else
HLinkAddr = HAddr & ": " & HSubAddr
End If
End Function

Sub TestLink()
Set Source = Range("A1")
Called = True
HLinkAddr Source
If Dir(HAddr, vbNormal) = "" Then
MsgBox "The hyperlink source file: " & vbCrLf & HAddr & vbCrLf & "does
not exist"
Else
MsgBox "The hyperlink source file: " & vbCrLf & HAddr & vbCrLf &
"exists."
End If
Called = False
End Sub

Cheers


"FrozenRope" wrote in message
...
I feel like a dork, but I'm not familiar with how to create a UDF or how
to
work with personal.xls. Is there a primer that you can direct me to that
can
help?

I have a 2,000 row spreadsheet where I need to extract the URL an embedded
column, and can't stand the thought of doing that by hand. ;o)

Thanks in advance for baby-sitting me on this!

"David McRitchie" wrote:

Hi Polb and Gary's,

If there is no hyperlink I think you would get zero

try

Function HyperlinkAddress(cell) As String
If cell.Hyperlinks.Count 0 Then _
HyperlinkAddress = cell.Hyperlinks(1).Address
End Function

to use:
=personal.xls!hyperlinkaddress(A1)

more informiation an variations see
http://www.mvps.org/dmcritchie/excel...perlinkaddress

---
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

"Gary''s Student" wrote in
message
...
If there is a hyperlink in C5, then the UDF
=hyp("C5") will return the URL


Here is the UDF:

Function hyp(r As String) As String
hyp = Range(r).Hyperlinks(1).Address
End Function
--
Gary's Student


"polytx" wrote:

How do I retrieve the url portion of a cell that has a hyperlink
stored int
it. The value function only returns the readable text.

How do we enter UDF (user defined function) into EXCEL and which menu do we
need to click?

Function hyp(r As String) As String
hyp = Range(r).Hyperlinks(1).Address
End Function


"Gary''s Student" wrote:

If there is a hyperlink in C5, then the UDF
=hyp("C5") will return the URL


Here is the UDF:

Function hyp(r As String) As String
hyp = Range(r).Hyperlinks(1).Address
End Function
--
Gary's Student


"polytx" wrote:

How do I retrieve the url portion of a cell that has a hyperlink stored int
it. The value function only returns the readable text.

http://www.mvps.org/dmcritchie/excel/install.htm

--


Regards,


Peo Sjoblom



"Tommy" wrote in message
...
How do we enter UDF (user defined function) into EXCEL and which menu do
we
need to click?

Function hyp(r As String) As String
hyp = Range(r).Hyperlinks(1).Address
End Function


"Gary''s Student" wrote:

If there is a hyperlink in C5, then the UDF
=hyp("C5") will return the URL


Here is the UDF:

Function hyp(r As String) As String
hyp = Range(r).Hyperlinks(1).Address
End Function
--
Gary's Student


"polytx" wrote:

How do I retrieve the url portion of a cell that has a hyperlink stored
int
it. The value function only returns the readable text.