Get Hyperlink Address with a Custom Function (UDF) in Excel
March 30, 2017
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 friendly hyplerlink text it had. I wanted the hyperlink URL 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.
pssst! If you do know of a way to do this with Excel’s regular formulas, please post a comment below to let us know!
You can follow along by copying this table and pasting into Excel:
Meme Title | Link |
---|---|
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… |
FYI, those links are just some random, funny memes I found on the web. I hope you enjoy them as much as I have.
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:
=GetHyperlinkAddress(B2)
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:
=HYPERLINK(GetHyperlinkAddress(B2))