We add hyperlinks in Excel to instantly access a particular file, web page, or any place within the document. We can also use them to create an email to the linked address or create a new document with just a click.
What’s cool about Excel is that it allows you to customize the texts displayed in the hyperlinks. You can edit them anytime to make them descriptive based on their purpose. This feature works best for the end user.
However, if you need to go over the URLs or the addresses of the linked web pages, this feature may be a bit of a nuisance as you can’t view them at one glance.
But don’t worry. There are ways for you to extract the URLs from these links without much difficulty.
Extract the URL from Hyperlinks in Excel through the Page’s Address Bar
The most straightforward way to get the URL of a hyperlink is through the address bar of the linked website.
1. Click on the hyperlink to open the web page.
2. As the web page opens, go to the address bar (located at the top).
Highlight the entire link and press CTRL + C to copy it.
3. Go back to your Excel file and paste the URL on the adjacent cell (or anywhere you like) by pressing CTRL + V.
And that’s it! You have successfully extracted the URL of the selected hyperlink.
This method works best if you only have one or two hyperlinks to work on and you don’t mind opening each web page.
Extract the URL from Hyperlinks in Excel through the Edit Hyperlink Menu
If you prefer not to open each webpage to get the URL, then this method is for you.
1. Select the cell containing the hyperlink.
2. Next, open the Edit Hyperlink menu.
There are three ways to do this:
- You can go to the Insert tab and click the Link button (under the Links section).
- You can also right-click on the cell, scroll down a bit and select Edit Hyperlink.
- Or, for the keyboard shortcut, you can simply press CTRL + K.
3. As the Edit Hyperlink menu opens, you will see the URL highlighted in the Address textbox at the bottom.
4. Right-click on it and select Copy.
5. Then, close the Edit Hyperlink menu to back to your sheet.
6. Select the adjacent cell (or any blank cell) and press CTRL + V to paste the URL.
And that’s it! You now have the URL in your sheet.
This method works best if you only have around five or more hyperlinks to pull the URL from.
However, if you have tens or hundreds of hyperlinks to work on, it would not be wise to use this method. You may want to make use of a macro or a custom function as described below.
Extract the URL from Hyperlinks in Excel Using a Macro
This method is perfect for those who have a long list of hyperlinks to extract the URLs from and who need to regularly perform this task.
1. The first step is to set up your list of hyperlinks.
Position your hyperlinks so that the cell on their right is blank.
NOTE: You don’t need to have hyperlinks positioned next to each other. They can be anywhere for as long as the cell on their right is empty because the macro will be adding the URLs on these cells.
2. Next, we need to add the macros. Press ALT + F11 to open the VBA Editor.
Go to the Insert menu and select Module.
3. Copy the following code and paste it on the new module added.
Sub ExtractURL()
Dim rng As Range, cl As Range
On Error Resume Next
Set rng = Application.InputBox(prompt:="Select the cells containing the hyperlinks.", Title:="Select Hyperlinks", Type:=8)
On Error GoTo 0
If Not rng Is Nothing Then
For Each cl In rng
If cl.Hyperlinks.Count > 0 Then
cl.Offset(0, 1).Value = cl.Hyperlinks.Item(1).Address
End If
Next
End If
End Sub
4. Next, we’ll add a button to our sheet so we can click on it whenever we need to run this macro.
Close the VBA Editor first. Once you’re back in your sheet, go to the Developer tab.
NOTE: If you can’t find the Developer tab in your ribbon, do the following:
- Go to File >> More >> Options.
- The Excel Options menu will appear. Go to the Customize Ribbon section.
- Tick the Developer checkbox and click OK.
From the Developer tab, click the Insert button under the Controls section.
Under Form Controls, click the button icon (first item in the list).
5. Then, click anywhere on your sheet where you would like to insert the button.
The Assign Macro menu will appear asking you to select the macro to assign to the button.
Select ExtractURL and click OK.
6. And that’s it! You’re all set to run the macro.
Feel free to rename the button by right-clicking on it and selecting Edit Text.
7. Now, let’s run the macro. Click the button. A prompt will appear asking you to select the cells containing the hyperlinks.
Drag your mouse to the sheet to select the cells containing the hyperlinks.
If you want to select cells that are not adjacent to each other, hold the CTRL key and select these cells.
You should then see the address of the selected cells added in the prompt.
Once you’ve selected all the required cells, click OK.
8. And that’s it! You should now have the URLs listed next to the hyperlinks.
9. Save the file in .xlsb or .xlsm format if you plan to keep the macro and reuse it later.
Extract the URL from Hyperlinks in Excel Using a Custom Function
If instead of a button, you prefer to extract the hyperlinks using a formula or a custom function, then this method is perfect for you.
1. Press ALT + F11 to open the VBA Editor.
2. Go to the Insert menu and select Module.
3. Copy the following code to the new module added.
Public Function GetURL(rng As Range)
If rng.Hyperlinks.Count > 0 Then
GetURL = rng.Hyperlinks.Item(1).Address
Else
GetURL = ""
End If
End Function
4. Close the VBA Editor and go back to your sheet.
5. On an empty cell, type the following formula: =GetURL(A2)
Change A2 with the address of the cell containing the hyperlink.
And that’s it! You now have the extracted URL.
6. If you have other hyperlinks to extract the URL from, simply copy this formula next to these cells.
In my example below, I have dragged the Fill Handler down to apply the same formula to the remaining cells.
And that’s it! You have now extracted the URLs from all the hyperlinks.
Conclusion
As you can see, there are plenty of ways to extract the URLs from the hyperlinks in Excel. Some methods are perfect for only a limited number of hyperlinks, while some would work even on a very long list.
Also Read: How to Remove Hyperlink in Excel