How to Extract Hyperlink Address (URL) in Excel Using VBA UDF

HomeVBAHow to Extract Hyperlink Address (URL) in Excel Using VBA UDF

Let’s say you got an Excel workbook from your colleague where you have a list of hyperlinks in a column. Now from this list, you want to extract all the hyperlink URL addresses.

The manual method is a real pain you need to copy-paste each URL one by one. So the better way is to create a UDF in VBA. And, today I’d like to share with you a simple code for this UDF.

VBA code to Extract Hyperlink URLs

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

How to use this Code

To use this UDF, you need to follow these steps:

  1. Open the visual basic editor from the developer tab or use the keyboard shortcut ALT + F11.
  2. Insert a new module, paste the above code into it and close it.
  3. Now in your worksheet, enter function [=GiveMeURL(A1)] and hit enter.

It will return the URL from the cell where you have a hyperlink and make sure to download this file from here.

More on VBA

VBA is one of the Advanced Excel Skills, and if you are getting started with VBA, make sure to check out there (What is VBA and Excel Programming).

1 thought on “How to Extract Hyperlink Address (URL) in Excel Using VBA UDF”

  1. Hi, I have a column E that contains Hyperlinks from row 2 to 25, or mabye just in row 3.
    Im supposed to check wich rows that contains a hyperlink and then get the links over to outlook mai.
    I’ve used A for 2 to 25, checking if cell is empty, but don quite get the link over from that cell containing hyperlink and into mail. How can I do that?

    Steinar

    Reply

Leave a Comment