How to Extract URLs from Hyperlinks in Excel

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.

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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

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

extract urls from hyperlinks1

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.

extract urls from hyperlinks2

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

extract urls from hyperlinks3

#7 let’s see the result:

extract urls from hyperlinks4

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.

extract urls from hyperlinks5

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.

extract urls from hyperlinks6

 

 

 

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar