Working with hyperlinks in Excel can be convenient for organizing data, but extracting the underlying URLs from these links isn’t straightforward. If you’ve ever needed to extract a URL from a hyperlink in Excel, you might have noticed that there isn’t a built-in function to do so. This guide walks you through creating a custom VBA function to achieve this effortlessly.
Why Extract URLs from Hyperlinks?
Hyperlinks in Excel often display friendly text, such as “Click Here,” while the actual URL is hidden. If you need to extract these URLs for analysis, sharing, or other purposes, manually copying them can be time-consuming. A custom VBA function can automate this process, saving you significant time and effort.
Steps to Extract a URL from a Hyperlink
Here’s a step-by-step guide to creating and using a custom VBA function to extract URLs from hyperlinks:
1. Open the Visual Basic Editor
To create the custom function, start by opening the Visual Basic Editor in Excel:
- For Windows: Press Alt + F11.
- For Mac: Press Option + F11 (or Fn + Option + F11).
2. Insert a New Module
Once the Visual Basic Editor is open, follow these steps:
- Locate your workbook in the Project Explorer panel. If you don’t see the panel, press Ctrl + R to open it.
- Right-click on the workbook name and select Insert > Module. A new module named “Module1” will appear.
3. Add the Custom Function Code
Copy the following VBA code and paste it into the blank window of Module1:
Function URL(HyperlinkCell As Range) As String
On Error Resume Next
URL = HyperlinkCell.Hyperlinks(1).Address
End Function
Once pasted, you can close the Visual Basic Editor and return to your Excel workbook.
4. Save the Workbook as a Macro-Enabled File
To ensure the custom function works in the future, save your workbook as an Excel Macro-Enabled Workbook (.xlsm):
- Go to File > Save As.
- Select Excel Macro-Enabled Workbook (*.xlsm) from the “Save as type” dropdown menu.
Note: When reopening the workbook, you may see a security warning. Click Enable Content to activate the macro.
5. Use the Custom Function
Now that the custom function is ready, you can use it like any other Excel formula:
- Assume the hyperlink is in cell B7.
- In another cell (e.g., C7), type:
=URL(B7). - Press Enter, and the URL will be extracted and displayed in the cell.
You can also drag the formula down to apply it to multiple rows of hyperlinks.
Important Notes
- Custom Function Scope: The custom function is stored within the workbook where it was created. To use it in other workbooks, you’ll need to recreate the function or save it in your Personal Macro Workbook.
- File Type: Always save the workbook as an .xlsm file to retain the macro functionality.
- Security Warnings: Enable macros when opening the workbook to ensure the custom function works as intended.
Conclusion
By following these steps, you can easily extract URLs from hyperlinks in Excel using a custom VBA function. This method simplifies what would otherwise be a tedious manual process. If you frequently work with hyperlinks, this trick can save you hours of effort. For more Excel tips and tricks, consider exploring additional resources or training programs to enhance your skills.
Source: Extract URL from Hyperlink with an Excel Formula – Excel University
