Extract URL from Hyperlink with an Excel Formula

Let’s say you have a hyperlink in a cell in Excel. The hyperlink may have friendly text, such as Click Here, but when you click the link it takes you to a URL such as https://www.excel-university.com. Now, let’s say you want to extract that URL from the hyperlink using an Excel formula.

Well … to my knowledge, there isn’t a built-in function to accomplish that. But, we can actually create our own custom function, and even name it URL if we’d like, using a few lines of code. I’ll walk you through each step so it will be easy to implement. And thanks to my friend Cary who asked how to extract a url from a hyperlink which led to this post!

Overview

Before we get too far, let’s confirm what we are trying to accomplish. We have a hyperlink, or maybe several hyperlinks, in some Excel cells. Like this:

We would like to be able to write some type of formula like =URL(B7) to extract the underlying URL from the links, like this:

Although (at the time of this writing) Excel doesn’t have a built-in URL function, we can create our own custom URL function using a few lines of code.

I’ll break the entire process down into bite-sized steps. Ready? Let’s do this.

Steps to extract a URL from a hyperlink

We’ll accomplish our objective with the following steps:

• Create the URL function
• Use the function to extract the URL
• Save workbook as XLSM

Let’s start with creating the custom URL function.

Create the URL function

We’ll need to add our custom URL function to the workbook.

Note: if you want to skip this step, I already created the URL function in the Sample File below. So, rather than creating it yourself, you can certainly just download the workbook and get on with it. But, if you are curious about how it works, keep reading and I’ll explain the details.

The first thing we need to do is open the Visual Basic Editor. This can be accomplish by using the Alt+F11 keyboard shortcut in Excel for Windows, and I believe Opt+F11 (or Fn+Opt+F11) in Excel for Mac.

Next, we need to insert a new Module into the workbook. To do so, locate the workbook in the Project Explorer panel … it should say something like VBAProject (Workbookname) like this:

Note: if you don’t see the Project Explorer panel, use the Ctrl+R keyboard shortcut to toggle it on.

Then, right-click the workbook name and select Insert > Module. You’ll see a new Module1 appear in a new folder called Modules, like this:

Next, double-click Module1 so it opens. You’ll see a blank window that feels a little bit like a word-processor because you can type stuff there. You could type the custom function code, but it will be faster to copy/paste. So, copy this VBA code:

Function URL(Hyperlink As Range)
  URL = Hyperlink.Hyperlinks(1).Address
End Function

And then paste it into Module1. It should look like this:

Believe it or not … the hard part is done!!!!!!!!!!!!!!!!

You can now toggle back to your Excel screen or close the Visual Basic Editor.

With the custom function complete, it is time to use it to extract the URLs from our hyperlinks.

Note: custom functions are stored inside workbooks rather than inside of the Excel application. This is good because other people that open the workbook can use the custom function. But, it also means you’ll either need to use this workbook for other URL extraction projects or create the custom function in other workbooks as needed.

Use the function to extract the URL from a hyperlink

To use the custom URL function, simply include it in a formula as you would with built-in functions. So, if our hyperlink was in B7, we could write the following formula in C7 to retrieve the URL from the hyperlink:

=URL(B7) 

Hit Enter, and bam…

We can also fill the formula down, and bam…

With our mission accomplished, we need to chat about file types and custom code.

Save workbook as XLSM

When you try to save or close the workbook, you’ll probably get a message like this:

This is basically telling you that if you want to be able to use the custom URL function in the future, you’ll need to save it as an XLSM file type instead of the default XLSX which is a macro-free file type.

So, first click No to the dialog above, and then change the Save as type option to Excel Macro-Enabled Workbook (*.xlsm) like this:

Once done, it means that the custom function will be successfully saved in the workbook. In the future, if you (or anyone else) opens the file you will have the ability to use the custom URL function to extract the URLs from hyperlinks.

When you (or anyone else) opens the workbook in the future, you may receive a security warning like this:

Be sure to Enable Content so that the URL function will work.

FREE: Excel Speed Challenge

If you enjoyed this post, please check out our free Excel speed challenge.

Watch one short Excel video a day for 5 days. Total video time is only 45 minutes. Learn the Excel skills that can help you save an hour a week.

Conclusion

Well, that is one way to extract a URL from a hyperlink using an Excel formula. If you have any other preferred methods or improvements to this one, please share by posting a comment below … thanks!

Sample file