 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008what formula will get the url from a cell with a hyperlink?
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  SignatureGary'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. 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 [quoted text clipped - 7 lines] > > 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! > Hi Polb and Gary's, > [quoted text clipped - 29 lines] > > > 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/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 [quoted text clipped - 38 lines] > > > > 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 > 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 [quoted text clipped - 38 lines] > > > > 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 > If there is a hyperlink in C5, then the UDF > =hyp("C5") will return the URL [quoted text clipped - 7 lines] > > 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 SignatureRegards, Peo Sjoblom > How do we enter UDF (user defined function) into EXCEL and which menu do > we [quoted text clipped - 16 lines] >> > int >> > it. The value function only returns the readable text. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.