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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
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
Click to expand...
 
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.


FrozenRope said:
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 said:
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

Gary''s Student said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top