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.
Table of Contents
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:
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.
Leave a Reply
You must be logged in to post a comment.