This post will guide you how to extract URLs from hyperlinks using VBA Macro in Excel. How do I extract the actual addresses from hyperlinks in the selected range with a User Defined Function in Excel.

1. Extract URLs from Hyperlinks with VBA Macro

Assuming that you have a list of data in range B1:B4, which contain hyperlinks. And you need to extract the actual addresses from those selected cells. How to do it. You can use an Excel VBA Macro code to achieve the result quickly. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub ExtractURLsFromHyperlinks()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that contain hyperlinks:", "ExtractURLsFromHyperlinks", myRange.Address, Type:=8)
    For Each myCell In myRange
        If myCell.Hyperlinks.Count > 0 Then
            myCell.Value = myCell.Hyperlinks.Item(1).Address
        End If
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 Select one Range that contain hyperlinks. Click Ok button.

#7 let’s see the result:

2. Extract URLs from Hyperlinks with User Defined Function

You can also write a User Defined Function to achieve the same result of extracting URLs from all of the selected hyperlinks in cells. Just do the following steps:

#1 repeat above steps 1-3.

#2 paste the below VBA code into the code window. Then clicking “Save” button.

Function GetURLAddress(myRange As Range) As String
    GetURLAddress = myRange.Hyperlinks(1).Address
End Function

#3 back to the current worksheet, then type the following formula in a blank cell, and then press Enter key.

=GetURLAddress(B1)

#4 drag the AutoFill handle over other cells to apply this formula to extract URLs.

3. Video: Extract URLs from Hyperlinks

This Excel video tutorial, we’ll explore two efficient methods to extract URLs from Hyperlinks: using a VBA macro and creating a User Defined Function . Both methods have their unique advantages and are designed to streamline the process of URL extraction, saving you time and effort.