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.
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!
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:
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.
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.
So these are two quick ways to extract URLs from hyperlinks in Excel.
Hope you found this Excel tutorial useful!
Other Excel tutorials you may find useful:
- How to Highlight Every Other Row in Excel (Conditional Formatting & VBA)
- How to Filter as You Type in Excel (With and Without VBA)
- How to Calculate Standard Error in Excel