The other day I copied a table on a website and pasted it into Excel. The table included hyperlinks, but I didn’t want to show the text it had. I wanted the hyperlink address instead. Here’s a picture of what I’m talking about:
Notice that the links have “…” at the end of the URLs, and cell in B2 says “A Link”. I wanted to see the entire URL for each one, which is buried underneath the Excel cell’s hyperlink.
If I hover over the hyperlink, I can see the full address:
The URL in the pop-up is what I would like to show in the cell, not the “friendly text” that it currently has.
Today, I’ll show you a quick tip to extract a hyperlink address from a cell with a User Defined Function (UDF). If you’re not familiar, a UDF is just a fancy way of saying a Custom Excel Function. And when I say “custom,” I mean using VBA.
That’s right, Excel doesn’t have a native function to get the hyperlink address from the cell, so we have to make a function ourselves. Let’s get right to it.
You can follow along by copying this table and pasting into Excel:
|When I try to take all the groceries at once||A Link|
|Using your microscope||http://images.memes.com/meme/13…|
|Relish that victory before they ketchup||https://giphy.com/gifs/cheezburger-hot-dogs-fun…|
Extract a Hyperlink Address with a Function
Open up the VBE (Alt+F11) and add a module (click on Insert -> Module).
Then add the following code:
Option Explicit Public Function GetHyperlinkAddress(rng As Excel.Range) As String Application.Volatile GetHyperlinkAddress = rng.Hyperlinks.Item(1).Address End Function
Now use this new function in your Excel worksheet. Here’s an example formula:
This will return the full address of the hyperlink, if any:
If you specify a cell that does not have any links, you will get an error:
One last thing
Even though the images show the formula results as hyperlinks (blue underlined text), these are not actually hyperlinks. If you want these to actually behave like hyperlinks, then change the formula to the following:
And that’s it. I hope you found this quick tip helpful!
Leave a comment below if you found this useful or have any questions.