Hi Excellers. Today I am going to show you how to create your very own Excel Function that will extract hyperlink addresses from any hyperlinks you have in your Excel worksheet.
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).
So let’s easily create our own Excel function sometimes known as User Defined Function. The code is shown below, and let’s step by step talk through the code.
FIRST 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 1. 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!
Want To Copy The Code?
If you want more Excel and VBA tips then sign up for my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.