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:
- Open visual basic editor from developer tab or use keyboard shortcut ALT + F11.
- Insert a new module, paste above code into it and close it.
- 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
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.