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.)
- In Excel 2010, select cell(s) that contain hyperlinks
- Right click on any selected cell
- 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.
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
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.
___________
Thanks!
Worked great for me. Thanks!
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.”
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
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
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.