How To Extract URL From A Hyperlink In Excel - The Fastest Way Possible!

Summary

In this tutorial, Jeff demonstrates how to extract URLs from hyperlinks in Excel using a custom VBA function. Since Excel lacks a built-in URL function, he guides viewers through the process of creating a VBA macro to retrieve the underlying URLs from hyperlinks. After explaining how to write and implement the function, he also covers saving the workbook in the macro-enabled `.xlsm` format to ensure the custom function persists. This tutorial is ideal for those looking to streamline their Excel workflow by automatically extracting URLs from hyperlinks.

Takeaways

• 😀 Extracting URLs from hyperlinks in an Excel workbook can be done using a custom formula.
• 😀 Hovering over a hyperlink reveals the underlying URL, but there is no built-in Excel function to extract this URL directly.
• 😀 The solution involves creating a custom VBA function to extract the URL from a hyperlink.
• 😀 To create the custom function, open the Visual Basic Editor by pressing Alt + F11, insert a new module, and write the code.
• 😀 The custom function can be written to return the URL of the hyperlink in the cell that calls it.
• 😀 Once the function is created, it can be used like any other formula in Excel, such as '=URL(A1)'.
• 😀 The custom function can be dragged down across multiple cells to extract URLs from multiple hyperlinks at once.
• 😀 The VBA code needs to be saved in a macro-enabled Excel file format (.xlsm) to ensure it is available for future use.
• 😀 If the workbook is saved as a .xlsx file, the VBA code will not be preserved, and an error will occur when trying to save.
• 😀 The custom function works only within the workbook in which the VBA code is saved, so for other workbooks, the VBA code must be copied over.
• 😀 The tutorial also encourages users to subscribe for more Excel tips and training, with the option to receive weekly tips via email.

Q & A

What is the purpose of the custom VBA function demonstrated in the video?

The purpose of the custom VBA function is to extract the underlying URL from a hyperlink in an Excel workbook, which doesn't have a built-in function for this task.

Why can't we use a simple Excel function to extract the URL from a hyperlink?

At the time the video was created, Excel doesn't have a built-in function for extracting the URL from a hyperlink, which is why a custom VBA solution is needed.

How do you open the Visual Basic editor in Excel to create the VBA function?

You can open the Visual Basic editor by pressing Alt + F11 on your keyboard.

What is the first step after opening the Visual Basic editor?

The first step is to right-click on the workbook you're working on and select 'Insert' -> 'Module' to create a blank module where you can write the code.

What is the VBA code used to extract the URL from a hyperlink?

The VBA code defines a function called 'url' that takes a range containing a hyperlink, extracts the hyperlink address, and returns it to the calling cell.

What does the formula '=URL(B7)' do in the example provided?

The formula '=URL(B7)' calls the custom 'url' function, passing the cell B7 (which contains a hyperlink), and returns the URL associated with that hyperlink.

What should you do to save the workbook with the VBA code included?

You need to save the workbook as an Excel Macro-Enabled Workbook (XLSM) because the default XLSX format does not support saving VBA code.

What happens if you try to save the workbook as an XLSX file after adding VBA code?

If you try to save the workbook as an XLSX file after adding VBA code, Excel will display a warning that the VBA code cannot be saved, prompting you to save it as an XLSM file instead.

Can the custom 'url' function be used in other workbooks?

No, the custom 'url' function is only available in the current workbook unless you copy and paste the VBA code into other workbooks where you want to use the function.

Why does the video emphasize changing the file format to XLSM?

The video emphasizes changing the file format to XLSM because only this format supports saving and running macros, which are essential for the custom 'url' function to work in the workbook.