what formula will get the url from a cell with a hyperlink?

[polytx]

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.

 

[Gary''s Student]

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

 

[David McRitchie]

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/buildtoc.htm#Hyperlinkaddress

 

[FrozenRope]

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!

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/buildtoc.htm#Hyperlinkaddress

 

[David McRitchie]

There is a reference on the referred to page that would point you to
http://www.mvps.org/dmcritchie/excel/getstarted.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

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!

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/buildtoc.htm#Hyperlinkaddress

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

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


:

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.

 

[macropod]

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

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!

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/buildtoc.htm#Hyperlinkaddress

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

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


:

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.

 

[Tommy]

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

 

[Peo Sjoblom]

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

--


Regards,


Peo Sjoblom