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:

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.

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.

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

Buy Me a Coffee at ko-fi.com

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

Wow, you read the whole article! You know, people who make it this far are true learners. And clearly, you value learning. Would you like to learn more about Excel? Please consider supporting me by buying me a coffee (it takes a lot of coffee to write these articles!).

Buy Me a Coffee at ko-fi.com

Written by Joseph who loves teaching about Excel.

© 2021, Spreadsheets Made Easy