How to Extract Hyperlink from URL in Excel (3 Methods)

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.


3 Methods to Extract Hyperlink from URL in Excel

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.

Extract Hyperlinks from URLs in Excel


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 builtin 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.

Extract Hyperlinks from URLs in Excel

  • To create a new module, choose the Module option from the Insert tab.

Extract Hyperlinks from URLs in Excel

  • 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.
=EXTRACTHYPERLINK(B5)

Extract Hyperlinks From URLs in Excel

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.

Extract Hyperlinks From URLs in Excel

Read More: How to Remove Hyperlink for Entire Column in Excel (5 Ways)


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

Extract Hyperlinks from URLs in Excel

  • Here is the list of extracted hyperlinks.

Read More: How to Get Hyperlink from an Excel Cell with VBA (3 Methods)


Similar Readings


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.

Extract Hyperlinks from URLs in Excel

  • 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.

Extract Hyperlinks from URLs in Excel

  • By following this process, we can get all other hyperlinks one by one.

Read More: How to Edit Hyperlink in Excel (5 Quick & Easy Ways)


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.

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

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo