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 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:

FYI, those links are just some random, funny memes I found on the web. I hope you enjoy them as much as I have.

Does this article help you? If so, please consider supporting me with a coffee ☕️

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