How to Extract URL from Hyperlinks in Excel (Using VBA Formula)

If you work with data copied from the web into Excel, you would often get a dataset that has hyperlinks in it.

For example, below is an example dataset where I have the names of some companies and these names are linked to the URL of that company.

Data from which URLs need to be extracted

Now, if I want to extract URLs from these hyperlinks in Excel, there is no in-built feature or formula to do this.

However, you can create your own formula using VBA to do this.

In this tutorial, I will show you a couple of easy methods to quickly extract the URL part from a hyperlink by creating a simple custom formula in VBA.

So let’s get started!

Table of Contents

Extract URL from Hyperlinks Using Formula

While there are more than 450+ functions in Excel, in case you can’t find one that suits your need, you can create your own custom formula as well.

Below is a dataset where I have the cells with hyperlinks and I want to extract the URL from each cell’s hyperlink.

Below is the VBA code that will create a custom function (ExtractURL) that can work just like a regular function in the worksheets:

Function ExtractURL(rng As Range) As String
On Error Resume Next
ExtractURL = rng.Hyperlinks(1).Address
End Function

Now let me give you the exact steps on how to add this VBA code in the:

Open the workbook where you want to add this VBA code
Hold the ALT key and press the F11 key (Opt + F11 or Fn + Opt + F11 if you’re using Mac). This will open the VB Editor window.
In the left part of the window, you should have the project explorer pane. In case it’s not there, you can click on the ‘View’ tab and then click on the ‘Project Explorer’ option
Right-click on any object for the workbook in which you want to get this function to extract URLs from hyperlinks
Go to Insert and then click on the module. This will add a Module to the workbook objects
Double click on the Module. This will open the code window for the module.
Copy and Paste the above code in the module code window
Close the VB Editor

Once you’re done with the above steps, you can now use the function ExtractURL in any cell in the worksheet.

With our dataset, I can use the following formula:

=ExtractURL(A1)

Using formula to extract URL from a cell in Excel

One good thing with using a formula is that it’s dynamic. So if you change the original data (or copy-paste new data from the web that has different hyperlinks), the formula would automatically update to give you the URL in the new dataset.

Note that when you add this code to a workbook, you can only use this newly created function in that workbook only. It won’t work in other workbooks where the code has not been added.

In case you want this code to work on all your workbook, you need to add this either to the Personal Macro workbook or create an add-in and add that add-in to Excel.

Also, when you add a VBA macro code to a workbook, you need to save it with the .XLSM (Excel macro-enabled) format.

Related: How to Remove Hyperlinks in Excel (Easy Step-by-Step)

Extract URL from Hyperlinks in Adjacent Cell using VBA Macro

There is another method you can use to extract all the URLs from cells that have hyperlinks.

While this also uses a VBA macro, it doesn’t create a formula. Instead, it simply uses the VBA code to go through each cell in the selection and extract the hyperlink from each cell in the adjacent cell.

Suppose you have the dataset as shown below and you want to get the URLs of these hyperlinks in the adjacent column.

Below is the VBA code to do this:

Sub ExtractURLs()
Dim lnk As Hyperlink
For Each lnk In Selection.Hyperlinks
    lnk.Range.Offset(0, 1).Value = lnk.Address
Next lnk
End Sub

The above code goes to each cell in the selection and if it has a hyperlink, it extracts the URL in the adjacent cell (done using the OFFSET property).

You need to place this code in a module (same steps as shown in the section above).

Once you have this code in the module, you can run this macro by clicking on the green run button in the toolbar or by selecting any line in the code and using the keyboard shortcut F5.