In this article, we’ll learn how to extract hyperlink from URL in Excel using three different methods. We often work with data from various sources that contain URLs. it could also happen in cases when we copy a table or a list from a website. Let’s go through the article to learn how to get the hyperlinks from these URLs.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
In this article, to show how to extract hyperlinks from URLs we’ll use a bunch of URLs from the Exceldemy website. These links represent some regular function names.
To extract hyperlinks from URLs, we can define a custom function in VBA code and then use it as a regular function. Excel doesn’t provide any built–in function so that we can get hyperlinks directly. Let’s follow the steps to get it done.
- From the Excel Ribbon, go to the Developer tab.
- Click on the Visual Basic option to open the Visual Basic Editor.
- To create a new module, choose the Module option from the Insert tab.
- Now, copy the following code in the code editor.
Function EXTRACTHYPELINK(Rng As Range) As String EXTRACTHYPELINK = Rng.Hyperlinks(1).Address End Function
With this code, we used the Hyperlinks constructor to create a custom function named EXTRACTHYPELINK that can be used as a regular function in our worksheet.
- In our dataset, we have 5 URLs in cells B5:B9.
- In cell C5, when we tried to type the function named EXTRACTHYPELINK, Excel provides us the user-defined function as an automatic suggestion. Press the Tab key to accept the suggestion and put B5 as the function argument.
- Otherwise, type the full function name by yourself. Write the formula in cell C5 and press Enter.
As a result, we can see the extracted URL in cell C5.
- To get the other URLs, locate the Fill Handle at the left bottom corner of cell C5 and drag it down.
Applying VBA code saves both time and effort when we want to extract hyperlinks from a number of URLs. Let’s say, we have 7 different URLs in cells B5:B11 from which hyperlinks are to be extracted.
- Put the following code in the Visual Code Editor:
Sub ExtractHLinksUrls() Dim Rng As Range Dim SelectRange As Range On Error Resume Next xTitleId = "Exceldemy" Set SelectRange = Application.Selection Set SelectRange = Application.InputBox("Range", xTitleId, SelectRange.Address, Type:=8) For Each Rng In SelectRange If Rng.Hyperlinks.Count > 0 Then Rng.Value = Rng.Hyperlinks.Item(1).Address End If Next End Sub
- Press F5 to run the code. A dialogue box opened to select the range of cells.
- Now, select cells B5:B11 to fill the Range input box and then click
- Here is the list of extracted hyperlinks.
- [Fixed!] This Workbook Contains Links to One or More External Sources That Could Be Unsafe
- How to Create a Drop Down List Hyperlink to Another Sheet in Excel
- How to Hyperlink Multiple Cells in Excel (3 Ways)
- Why Do My Excel Links Keep Breaking? (3 Reasons with Solutions)
- [Fixed!] ‘This workbook contains links to other data sources’ Error in Excel
Using the Edit Hyperlink to extract hyperlinks is a manual process that would cost some valuable time and effort. Still, it is a useful method to know. Let’s see how we can extract a hyperlink from an URL by using this technique. The steps are given below.
- Click on the cell containing the URL to be extracted. Here, we selected cell B5.
- Right-click the mouse will open up the context menu and then select the Edit Hyperlink.
- The above steps opened up the Edit Hyperlink window. The Address input box shows the hyperlink.
- Press Ctrl + C to copy the hyperlink and click OK to close the window. After that, paste the copied link in the desired cell. We pasted the hyperlink associated with cell B5 in cell C5.
- By following this process, we can get all other hyperlinks one by one.
Things to Remember
- Although using VBA code is great to accomplish our goal. But once the code is run, we lose the history. It means we cannot undo the change anymore.
- In case we need to change our source data from time to time, it’s better to use methods that use functions as we used in method 1. In this case, the output is dynamic with the change of the source data.
Now, we know several methods to extract hyperlinks from URLs in Excel. Hopefully, it would encourage you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
- Hyperlink in Excel Not Working (3 Reasons & Solutions)
- Hyperlink in Excel VBA: Properties and Applications
- [Fixed!] Break Links Not Working in Excel (7 Solutions)
- Excel Hyperlink to Another Sheet Based on Cell Value
- How to Create Dynamic Hyperlink in Excel (3 Methods)
- How to Hyperlink to Cell in Excel (2 Simple Methods)