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.

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 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)

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.

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.

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.

Extract Hyperlinks from URLs.xlsm

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.

<< Go Back To Hyperlink in Excel | Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

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

How to Extract Hyperlink from URL in Excel: 3 Methods

1. Create a User Defined Function to Extract Hyperlink from URL in Excel

2. Excel VBA Code to Get Hyperlink from Url

3. Extract Hyperlink from Url Using Edit Hyperlink Feature in Excel

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply