Get the URL from an Excel Hyperlink

iconhyperlink Last week on the Bacon Bits blog, Mike Alexander showed how to send an email with the HYPERLINK function in Excel, complete with subject line and message. Mike's article showed how versatile the HYPERLINK function can be, and you also learned about Mike's unique talent for poetry. Remember, just because you have a gift, don't feel obligated to share it! ;-)

Remove Hyperlinks

If you inherit a workbook full of hyperlinks, and you want to change those, so they just contain text, you can copy the cells, and paste them as values, in another location. If you're using Excel 2010, you can quickly remove hyperlinks from selected cells. (Thanks to Sam for this tip.)

  1. In Excel 2010, select cell(s) that contain hyperlinks
  2. Right click on any selected cell
  3. Click Remove hyperlinks

In any version, to remove hyperlinks in a group of selected cells, you can run a macro, like the one below. Copy this code into a regular module in your workbook, then select the cells, and run the delHyperlinks macro.

Sub delHyperlinks()
  Selection.Hyperlinks.Delete
End Sub

You can also clear all the hyperlinks on the active worksheet (thanks Eric and Rick).

Sub RemoveHyperlinksOnActiveSheet()
'posted by Rick Rothstein
  Cells.Hyperlinks.Delete
End Sub

Extract Address from Hyperlink

If you paste hyperlinks as values, what you're left with is the "Friendly Name." That's the text you can see in the cell with the hyperlink, like "Sales Report" in the screen shot below.

HyperlinkDrag04

Instead of the Friendly Name, you might want to extract the hyperlink's location. There's no built-in function for that in Excel, but you can create your own, with a bit of VBA.

Copy the HLink code, shown below, into a regular module in your workbook.

Function HLink(rng As Range) As String
'extract URL from hyperlink
'posted by Rick Rothstein
  If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
End Function

Then, you can use the HLink function in that workbook, just like any other Excel function. For example, to find the address for a hyperlink in cell B3, use this formula: =HLink(B3)

To extract the Friendly Name, use a simple link to the cell: =B3

hyperlinkextract01

Learn More About Hyperlinks

To learn more about adding and removing Hyperlinks, visit the Excel Hyperlinks and Hyperlink Function page on the Contextures website. There are written instructions, and videos, like this one.

___________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

26 comments to Get the URL from an Excel Hyperlink

  • Rick Rothstein (MVP - Excel)

    @Deb,

    I don't think you need to use an error trap to extract the hyperlink address; I believe this one-liner will work just as well...

    Function HLink(rng As Range) As String
    If rng.Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
    End Function

    Note that I declared my function's return value "As String" so that when the If statement test is False (a count of zero), the return value becomes the default for String variables, namely, the empty string... when the count is not zero (meaning there is a hyperlink), the hyperlink's address is returned.

  • Rick Rothstein (MVP - Excel)

    @Deb,

    There is a minor flaw in both your blog's HLink function and the one I just posted. If a range containing multiple cells is specified, then both of our functions will return the first hyperlink address if the range has one or more hyperlinks in it... no matter where in the range that first hyperlink happens to be. I think the most reasonable "fix" is to make our functions look at only the first cell in the specified range so that if that cell does not have a hyperlink, then the empty string is returned for the whole range. The fix is easy... we just change the rng reference to rng(1). Since your function only has one rng reference, that is the change you make whereas for my function, only the If..Then test's rng reference needs to be change. So then, here is my revised function...

    Function HLink(rng As Range) As String
    If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
    End Function

  • sam

    The Delhyperlinks is required only for Excel2007 and below.

    From Excel 2010 if you select a range of Cells with hyperlinks and right click – and remove hyperlinks – this removes from the entire range

  • Rick Rothstein (MVP - Excel)

    @Deb,

    I think I have another one-liner for you. It appears that the following macro will do what your delHyperlinks macro does...

    Sub delHyperlinks()
    Selection.Hyperlinks.Delete
    End Sub

  • Eric

    I've had a sub for removing hyperlinks in my personal.xla for a while. Here's the main code for it:


    While Application.Worksheets(Application.ActiveSheet.Name).Hyperlinks.Count > 0

    Application.Worksheets(Application.ActiveSheet.Name).Hyperlinks(1).Delete

    Wend

    I generally don't use a selection, and just want to remove all the links from a certain sheet, so this has worked for me. I guess you could change the application.worksheets to selection.

  • Rick Rothstein (MVP - Excel)

    @Eric,

    If you want to delete all the hyperlinks on the ActiveSheet, you can use this much simpler macro (modeled after the delHyperlinks macro I posted to Deb)...

    Sub RemoveHyperlinksOnActiveSheet()
    Cells.Hyperlinks.Delete
    End Sub

  • Thanks Rick, Sam and Eric -- I'll update the blog post with the revised code examples.

  • Ron

    This worked like a charm. I needed to refer to the YouTube video for creating the module and pasting the code, complicated by my using Excel 2003, but it took only 30 minutes and will save lots of time searching the underlying URLs copied as friendlies. Thanks to all. Good stuff!

  • Todd

    I'm dealing with something a little different - I have links but not to websites; rather, to file share folders. I am getting a #NAME? error. Does the code need to be altered due to this difference?

  • I've just spent some thirty minutes to uncover that the fragment part of URL (the one after hash # symbol) is available through the Hyperlinks(1).SubAddress . I hope that helps.

  • Sarah

    When I use the function

    If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address

    It returns the hyperlink for the email address. However, it returns it with the "mailto:" included as part of the link.

    Is there a line of code I can add to the function that only links the email address and not the "mailto:" that excel adds?

    Any help is appreciated.

  • @Sarah,

    Why not just wrap the expression with a Replace function call replacing "mailto:" if it is there...

    If rng(1).Hyperlinks.Count Then HLink = Replace(rng.Hyperlinks(1).Address, "mailto:", "")

  • [...] original was a one-liner posted by Rick Rosthstein on Debra Dalgleish’s Contextures Blog.  I have just added an option to return the “sub-address” (part following the #), [...]

  • Alma

    amazing, work just as shown.

  • Tom Paquette

    Thanks! This was very helpful to me.

  • Eshrat

    Awsome! did exactly what I needed.

  • Jay Essel

    Thank you. That's a very neat way to get at the URL from a cell with a hyperlink. I had spent a few minutes trying to find way to do it with 'normal' excel functions, and was getting nowhere.

  • Daniel

    Help a lot, thank you very much!

  • Tyler

    I had a hard time getting this to work (#NAME? error) until I saved, exited, and reopened the Excel file after creating the macro...don't know if this is a legitimate step. Also, for other newbies, I had to enable macros: [Top left Office Logo]>Excel Options>Trust Center>Trust Center Settings [Bottom right]>Macro Settings> Enable... AND Trust access to VBA (but I'm not sure about that last one). I also had to Save As and select Microsoft Office Excel Macro-Enabled Worksheet as opposed to the default macro-disabled file.

    Thanks.

  • Marc

    There's a minor bug with HLink() in that if the link contains a # then it only returns the part before the # sign. To get the full URL it should be coded as:

    Function HLink(rng As Range) As String
    'extract URL from hyperlink
    'posted by Rick Rothstein
    If rng(1).Hyperlinks.Count Then
    HLink = rng.Hyperlinks(1).Address
    if (Len(rng.Hyperlinks(1).SubAddress) > 0) then HLink = HLink & "#" & rng.Hyperlinks(1).SubAddress
    End Function

    While that code is better there's still a bug in that Excel allows a cell to have multiple hyperlinks. The current code only returns the first hyperlink address.

  • Neil

    Is there a way of getting this to work for cells that contain the hyperlink function? I'm trying to extract the path from the following:
    =HYPERLINK(A21&B21&J21&" - "&TEXT(Date,"yyyy-mm")&".xlsm",J21&" - "&TEXT(Date,"yyyy-mm"))

    I've tried EVALUATE(ActiveCell.Formula) but this just gives me the Friendly name, rather than the link location

  • Thanks for the HLINK function. Worked so nicely at the first attempt.

  • Neruad1977

    It works fine but does not activates and showing result automatically as normal excel function - COuld you please advise what is wrong?

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>