Let’s assume you have a hyperlink in an Excel cell. Although the hyperlink may include pleasant content such as ‘Click Here’ when you click it, you will be taken to a URL such as https://techpady.com.
Let’s imagine you wish to use an Excel formula to extract the URL from the hyperlink.
To my understanding, there isn’t a built-in feature that allows you to do that. However, with a few lines of code, we can construct our own custom function and even name it URL if we want.
I’ll guide you through each step so you can get started right away.
To get this done, we need to go through a series of steps and procedures to help us extract the URL from the hyperlinks.
We first begin with creating a URL function, followed by using the function to extract the URL and then saving the spreadsheet workbook as XLSM.
How to create a URL function to extract hyperlinks in excel
Just as I mentioned earlier, we have to create our custom function to help us extract the hyperlinks in any Excel spreadsheet. Follow the steps below to create the hyperlink extractor custom formula.
- Open the visual basic by using the keyboard combination Alt+F11.
- Insert a new module into the new workbook panel. The new work panel should look like the one displayed below
- Right-click the workbook name and select Insert > Module. A new module 1 will appear like shown below
After that, double-click Module1 to bring it up. You’ll notice a blank window that looks and feels like a word processor because you can type in it. You could input the custom function code, but copying and pasting it will be faster.
Copy the following VBA code:
And paste it into the Module 1 working area. It should look like this
You can either return to your Excel screen or close the Visual Basic Editor at this point.
Now that the custom function is finished, we can use it to extract URLs from our hyperlinks.
It is great to take note that, Workbooks, not the Excel application, are where custom functions are stored. This is beneficial since the custom function can be used by anyone who opens the workbook.
However, you’ll have to either use this worksheet for subsequent URL extraction tasks or develop the custom function in other workbooks as needed.
Now that we have successfully created our custom hyperlink extractor function, let’s proceed to use it to extract hyperlinks from our workbook.
In order to do this, simply include the function in a formula. So the custom function to be used here is
=URL(B2) in our case. Your hyperlink could be in any of the cells.
Use the fill handle to drag down the subsequent cells to apply the same function to the other hyperlinks.
Excel might not allow you to save workbooks with this format or features. Therefore, you might get a pop-up notification like this when you try to save the excel worksheet.
This essentially means that if you want to utilize the custom URL function in the later, you must save the file as an XLSM file type rather than the normal XLSX, which is a macro-free file type.
So, in the window above, select ‘No’, and then change the ‘Save as type’ choice to Excel Macro-Enabled Workbook (*.xlsm) as follows:
Once completed, the custom function will be stored successfully in the workbook. If you (or anybody else) open the file in the future, you will be able to utilize the custom URL function to extract URLs from hyperlinks.
You (or anybody else) may receive a security warning like this in the future when you access the workbook:
If you want the URL function to work, make sure you enable content.
This is a straightforward tutorial with images to guide you in order to complete your task effortlessly, simply, and fast enough. Hope this was useful.