If you need to add a hyperlink in Excel 2016 or 2013, you can choose one of the following hyperlink types: a link to an existing or new file, to a web page or e-mail address. Excel internal hyperlinks will help you to quickly jump to the necessary part of the workbook without hunting through multiple worksheets. One of the ways you can put spreadsheet hyperlinks to good use is to create a table of contents of your workbook. But do you know the benefits of spreadsheet hyperlinks in Excel workbooks? The time has come to discover them and start using this great Excel feature. Clicking on hyperlinks you instantly get access to other information no matter where it is located. If you are a real Internet surfer, you know firsthand about the bright sides of hyperlinks.
If you don't need a hyperlink any more, you'll see how to quickly remove it. You'll also learn how to change a link destination and modify its format.
Once you have this code in the module, you can run this macro by clicking on the green run button in the toolbar or by selecting any line in the code and using the keyboard shortcut F5.In this article I'll show you 3 ways how you can add hyperlinks into your Excel workbook to easily navigate between numerous worksheets. You need to place this code in a module (same steps as shown in the section above). The above code goes to each cell in the selection and if it has a hyperlink, it extracts the URL in the adjacent cell (done using the OFFSET property). Suppose you have the dataset as shown below and you want to get the URLs of these hyperlinks in the adjacent column.īelow is the VBA code to do this: Sub ExtractURLs() Instead, it simply uses the VBA code to go through each cell in the selection and extract the hyperlink from each cell in the adjacent cell. While this also uses a VBA macro, it doesn’t create a formula. There is another method you can use to extract all the URLs from cells that have hyperlinks. Related: How to Remove Hyperlinks in Excel (Easy Step-by-Step) Extract URL from Hyperlinks in Adjacent Cell using VBA Macro In case you want this code to work on all your workbook, you need to add this either to the Personal Macro workbook or create an add-in and add that add-in to Excel.Īlso, when you add a VBA macro code to a workbook, you need to save it with the. It won’t work in other workbooks where the code has not been added.
Note that when you add this code to a workbook, you can only use this newly created function in that workbook only.
So if you change the original data (or copy-paste new data from the web that has different hyperlinks), the formula would automatically update to give you the URL in the new dataset. One good thing with using a formula is that it’s dynamic. With our dataset, I can use the following formula: =ExtractURL(A1) Once you’re done with the above steps, you can now use the function ExtractURL in any cell in the worksheet. Copy and Paste the above code in the module code window.
This will open the code window for the module. This will add a Module to the workbook objects
Now let me give you the exact steps on how to add this VBA code in the: While there are more than 450+ functions in Excel, in case you can’t find one that suits your need, you can create your own custom formula as well.īelow is a dataset where I have the cells with hyperlinks and I want to extract the URL from each cell’s hyperlink.īelow is the VBA code that will create a custom function (ExtractURL) that can work just like a regular function in the worksheets: Function ExtractURL(rng As Range) As String Extract URL from Hyperlinks in Adjacent Cell using VBA MacroĮxtract URL from Hyperlinks Using Formula.Extract URL from Hyperlinks Using Formula.