Get the URL from an Excel Hyperlink

Get the URL from an Excel Hyperlink

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.

In the steps below, I’ll show you how to get the URL from an Excel Hyperlink.

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

Macro to Remove Selected 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

Macro to Remove All Hyperlinks

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.

Hyperlink on worksheet shows friendly name
Hyperlink on worksheet shows friendly name

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 User Defined Function, 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

Use the HLink 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.

___________

35 thoughts on “Get the URL from an Excel Hyperlink”

  1. @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.

  2. @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

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

  4. @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

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

  6. @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

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

  8. 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?

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

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

  11. @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:”, “”)

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

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

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

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

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

  17. Any ideas on how to get this to work with a linked workbook using a data connection? If I use just the source file of course it works great.
    =HLink(‘\\network share\[sourcefile.xlsx]WorkBook’!$C$2)
    2 files one target and one source.
    I have the formula in my target file, I added the function to the target file, and the source file contains the cell with the embedded URL.
    Error: “Value used in the formula is the wrong data type.”

  18. Hi All,
    it’s 3 days I’m struggling with hyperlinks and VBA and finally I found you Guys!!! Thank you so much
    Just a comment about the code to find the link in a range. Let’s say you are looking for a link in 5 cells and you don’t know its location, then you will need to loop into the range… I did this, hope this is useful for you too:
    Function HLink(rng As Range) As String
    ‘extract URL from hyperlink
    ‘posted by Rick Rothstein
    Dim c As Range
    For Each c In rng
    If c(1).Hyperlinks.Count Then HLink = c.Hyperlinks(1).Address
    If HLink “” Then Exit For
    Next
    End Function

  19. Hi. New to hyperlinks and keen amateur with spreadsheeting rather than ptofessional. I have build a spreadsheet to show the lineage of a dairy herd. “raw” data showing calf, sire, dame for each animal on the place are on one page. Some of these animals have pedigree certificates that I have scanned into another folder and attached them with hyperlinks to the specific animal on the raw data sheet. I then basically use vlookup to manipulate this raw data on different sheets in the workbook to see breeding lines etc.
    Is it possible to have the hyperlink attached to the animal on the raw data page to follow it when that animal is picked up by a vlookup formula on another page?
    Many thanks,
    Mike

  20. Mike asked:
    > Is it possible to have the hyperlink attached to the animal on the raw data page to follow it when that animal is picked up by a vlookup formula on another page?
    That depends on what you have on the raw data page. I’ll assume column “H” on the raw sheet has the link to your certificate file.
    The answer is yes, you can get the link URL using vlookup() if column H has one of the following:
    file://c:/certificates/mycert.pdf
    =”file://c:/certificates/mycert.pdf”
    =HYPERLINK(“file://c:/certificates/mycert.pdf”)
    The answer is no, you can’t get the link URL using vlookup() if column H has the following:
    =HYPERLINK(“file://c:/certificates/mycert.pdf”,”mycert.pdf”)
    With the first three the URL is visible and can be extracted/used with VLOOKUP(). With the last one only “mycert.pdf” is displayed and the underlying link URL is hidden. That link can’t be extracted using the HLink() function that’s the subject of this talk page thread.
    I’ll assume the following:
    Raw!A:A contains an index number that’s unique for each animal.
    Raw!H:H contains the URL to the animal’s pedigree certificate.
    We have another worksheet named “ForSale” with ForSale!A:A containing the animal index number. In that case you can link to the pedigree certificate from ForSale using:
    =HYPERLINK(VLOOKUP(A2,Raw!A:H,COLUMN(Raw!H:H),0))
    I use COLUMN(Raw!H:H) rather than hard coding “8” as that allows me to add/remove columns from my Raw worksheet and the references to this worksheet will automatically be updated. This assumes the Raw index will always be column A. If you think you will be inserting a column before the index then use
    =HYPERLINK(INDEX(Raw!H:H,MATCH(A2,Raw!A:A,0)))
    In that example I’m not using VLOOKUP() but it allows you to add/remove columns from Raw at will and Excel will automatically update the references to the data on Raw.
    If instead your your Raw worksheet has
    =HYPERLINK(“file://c:/certificates/mycert.pdf”,”mycert.pdf”)
    then it will not be possible to extract the link other than by extracting the formula and reverse engineering whatever it does. One of the internal properties of a cell is the Hyperlinks field. If a cell is linked using “Insert Hyperlink” then we can extract the link URL from the Hyperlinks field. If someone uses the HYPERLINK() function rather than “Insert Hyperlink” the cell’s Hyperlinks field is empty meaning the link URL is not available to VBA.

  21. Mike, when I woke up this morning I realized there is one more variant and it is one where you use HLink() function from the top of this thread.
    If in Raw column H you have the value mycert.pdf and did insert-hyperlink to C:\certificates\mycert.pdf then it will display mycert.pdf and when you click it’ll open the PDF file assuming the file exists. To use this link from another worksheet you would have:
    =HYPERLINK(hlink(INDEX(Raw!H:H,MATCH(A10,Raw!A:A,0))),INDEX(Raw!H:H,MATCH(A10,Raw!A:A,0)))
    I am using INDEX(Raw!H:H,MATCH(A10,Raw!A:A,0)) twice. The first time I pass the result to HLink() which extracts the URL and the second time it’s used to extract the display text. I’m not using the VLOOKUP() function in that example as VLOOKUP() does not return a range object that can be used with HLink(). We can use VLOOKUP() to get the cell’s display text though.
    =HYPERLINK(hlink(INDEX(Raw!H:H,MATCH(A10,Raw!A:A,0))),VLOOKUP(A10,Raw!A:H,COLUMN(Raw!H:H),FALSE))
    Using INDEX(Raw!H:H,MATCH(A10,Raw!A:A,0)) is more portable than VLOOKUP(A10,Raw!A:H,COLUMN(Raw!H:H),FALSE) as it allows you to move the index column around. It does not need to be on Raw!A:A and can be after the column with your data.
    A better coding for HLink() is
    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
    Else
    HLink = rng(1).Value
    End If
    End Function

    I made two changes from the original. 1) The line with SubAddress accounts for hyperlinks that contain a #. Excel put the part after the # in the SubAddress field. 2) If the field does not contain a hyperlink I return its value. This allows hlink() to be used with hyperlink() if your certificate column has things such as c:\certificates\mycert.pdf or file://c:/certificates/mycert.pdf. The second change also allows you to use =HYPERLINK(“c:\certificates\mycert.pdf”) in the certificates column. You still can’t use =HYPERLINK(“c:\certificates\mycert.pdf”,”mycert.pdf”) as Excel does not give us a way to extract the hyperlink URL when the HYPERLINK() function is used.

  22. Thankyou again. I think this is getting a bit above my pay scale at the moment (I am teaching myself as I go having been a +-*/ type spreadsheet user prior to this!) but I will sit down and try and work it out bit by bit. A part of my problem is that not all cows in the herd have pedigrees so not all the raw data has a hyperlink and the second is that your assumption of a discrete value in “H” is correct but not everything with a certificate is in H. I have the raw data set up in columns of “calf”, “sire”, “dame”. A dame and often a the sires are outside stud animal and never actually show up in the discrete H (calf) column i.e. when I am in the “lineage” sheet which picks up up to 10 generations of maternal lineage for any particular animal (using vlookup to find each dame’s breeding as I go back through the generations).
    Anyway from my primitive understanding I may still be able to apply your principles to my situation. Give me a few days to “play” with it and I will see how I go.
    Again, thank you very much for your time and effort.
    Mike

  23. Mike, it’s off topic for this thread but you will discover that if you use =H23, INDEX(), VLOOKUP(), etc, to get the data from cell H23 and that cell is empty that a “0” (zero) gets displayed. It’s a bit of Excel horribleness in that you get a zero when you expect a blank. The fix I do is =IF(LEN(H23),H23,""). That’s simple enough unless the expression to get cell H23 is rather long and you end up with =IF(LEN(long-expresssion),long-expresssion,""). There’s no getting around having the expression twice though you could put it in its own column and test for zero. At http://support.microsoft.com/en-us/kb/214244 Microsoft suggests that you use ISBLANK() and to code it as =IF(ISBLANK(H23),"",H23) That works too. I prefer using LEN() as I don’t need to deal mentally with the negative logic introduced by ISBLANK().
    I brought that up because if you use blank cells for those animals that don’t have a pedigree certificate then you will be needing to first check with LEN() or ISBLANK() to see if a pedigree certificate is available.
    You may want to consider learning and using Microsoft Access instead of Excel. Access simplifies the process of dealing with records that have relationships to other records as you seem to have with ten generations of sires and dames. You can have relational records in Excel but end up managing the indexes and relationships manually. Your spreadsheet will get slower and slower as functions such as VLOOKUP() and MATCH() end up doing scans of many rows looking for matches. Access constructs internal lookup/index tables meaning references from one table to another take next to zero time regardless of how many records you are dealing with. A related issues is your cell expressions can get very long and as Excel does not allow for comments or formatting to help make the expressions readable to humans they end up being painful to understand and debug. You will also discover that Excel has limits on how far you can nest things.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.