Get Hyperlink Address in UDF

Get Hyperlink Address with a Custom Function (UDF) in Excel

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:

Sample Data

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:

Sample Data - hover

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.

P.S. 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).

Open the VBE

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:

GetHyperlinkAddress function

If you specify a cell that does not have any links, you will get an error:

GetHyperlinkAddress 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))

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.

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!

Suggest the next post!

I would love your help to know what I should post about next, so if you enjoyed this content and would like to see more, please let me know what you'd like me to talk about in the comments below. Thank you!