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.
How to Extract Hyperlink from URL in Excel: 3 Methods
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.
1. Create a User Defined Function to Extract Hyperlink from URL in Excel
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.
Steps:
- 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 FunctionWith 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 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.
=EXTRACTHYPERLINK(B5)
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.
Read More: How to Link a Website to an Excel Sheet
2. Excel VBA Code to Get Hyperlink from Url
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.
Steps:
- 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.
Read More: How to Add Hyperlink to Another Sheet in Excel
3. Extract Hyperlink from Url Using Edit Hyperlink Feature 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.
Steps:
- 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.
Read More: How to Edit Hyperlink in ExcelÂ
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
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.
Related Articles
- How to Find and Replace Hyperlinks in Excel
- [Fix:] Hyperlink to Website Not Working in Excel
- Hyperlink in Excel Not Working
- How to Solve ‘Cannot Open the Specified File’ Error in Excel Hyperlink
- [Fixed!] Hyperlinks in Excel Not Working After Saving
- [Fixed!] Excel Hyperlink Keeps Coming Back
- [Fixed!] Excel Hyperlink Formula Not Working
- [Fixed!] Excel Hyperlink Is Not Redirecting Properly
