Hi Excellers. Would you like to know how to create your very own Excel function that extracts hyperlink addresses? If so, then read on. In this blog post, we will show you how to create such a function. We will also provide some helpful tips on how to use it. So without further ado, let’s get started and extract any hyperlinks you have in your Excel worksheet.
The Problem. How Do I Extract Hyperlinks?.
So, unfortunately, Excel does not have a built-in function that will extract web addresses from hyperlinked cells. If you have downloaded or copied information from a web page you may have the hyperlink in Excel, a bit like in my screenshot below.
But, if you need to see the actual URL then manually copying and pasting them from the Edit Hyperlink menu can be very time-consuming. (Right Click | Edit Hyperlink). This is especially inconvenient if there are multiple hyperlinks.
So let’s easily create our own Excel function. These are sometimes known as User Defined Functions (UDF). The full Excel VBA code is shown below. But, let’s step by step talk through the code together.
Before we begin to write the Excel function, the Visual Basic Editor is required. This editor is is the Visual Basic Tab. Please read watch my YouTube HERE on how to enable the Developer Tab if you do not see it.
Prepare To Write Your UDF
Ok, so now you have the The Developer Tab active, open the Visual Basic Editor. You can do this either by hitting ALT+F11 or hit the Developer Tab | Code Group | Visual Basic. Next, insert a new Module to store your VBA function code. This is Step 1. Select the Insert Menu | Select Module to create a New Module.
Step 1. Insert A New Module.
The first step is to Insert a Insert a new module. If you want to use your function only in the current worksheet then you do not need to insert a module, but if you want to use the function in other worksheets of the same workbook then you will need to insert the code into a module.
Step 2.
The first piece of code instructs Excel that you are defining a function. In this example it is GetHyperlinks, and that the function will work with a range of cells that we define as HyperlinkCell
Step 2. This piece of code specifies that when a runtime error occurs Excel goes to the statement immediately following the statement where the error occurred.
Step 3. Excel uses the range and will go to the first hyperlink and return the address of that hyperlink.
Step 4. If there no hyperlink in the cell then Excel will nothing. The cell will appear blank.
Step 5. This line of code tells Excel that it has reached the end of the function
Step 6. Test your Macro!