3 Best Ways to Extract a URL From Hyperlinks in Microsoft Excel

The data entry and analysis features of Microsoft Excel are well known to its users. Users can create forms and charts and break down large data sets using the functions and formulas within an Excel spreadsheet. However, an often overlooked capability of Excel is its data extraction feature. For example, using the Power Query function in Excel, users can extract data from various online or offline sources.

Additionally, if you are working with a large data set of hyperlinks, there are various ways to extract the URLs, too. We’ve explained three popular methods below, so let’s take a look.

How to Extract a URL From Hyperlinks Using the Context Menu

The context menu, also known as the right-click menu in Microsoft Office programs, has a series of shortcuts for performing actions. To extract a URL from a hyperlink in Microsoft Excel, right-click on the hyperlink and choose “Edit Hyperlink” from the context menu. This will open a dialog box where you can see and edit the hyperlink’s URL. Here’s how:

Step 1: From your PC’s Start menu or Taskbar, click the Microsoft Excel app or workbook with the hyperlinks to open it.

Step 2: Click on the Sheet tab with the hyperlinks and select the cell with the hyperlinks.

Step 3: Right-click the cell with the hyperlink to launch the context menu.

Step 4: Select Edit Hyperlink to launch the Edit Hyperlink dialog box.

Step 5: Place your mouse cursor in the Address field of the Edit Hyperlink dialog box to highlight the URL.

Step 6: Right-click the highlighted URL and select Copy. Alternatively, you can use the shortcut Ctrl + C to copy the URL.

Step 7: Click OK or Cancel to close the Edit Hyperlink dialog box.

Step 8: Right-click the Excel document or the document where you would like to place the extracted links and select Paste. Alternatively, paste the URL by using the Ctrl + V shortcut.

And just like that, you will be able to extract URLs from a hyperlink. Note that using the context menu to edit and extract hyperlinks in Microsoft Excel is only advisable when dealing with a small data set.

How to Extract a URL From Hyperlinks by Using Word and HTML

You can use Word and an HTML reader like Notepad if you need to extract hyperlinks from a moderately sized data set. Copy the hyperlinks from your Excel to a Word document, then save the Word document as HTML. Here’s how you can then extract the URLs from the HTML file.

Step 1: From the Start menu or Taskbar of your PC, click the Microsoft Excel app or workbook with the hyperlinks to open it.

Step 2: Click on the Sheet tab with the hyperlinks and copy all cells with the hyperlinks.

Step 3: Open a new Word document and paste the copied hyperlinks into the document.

Step 4: Click the Ctrl + S keys to launch the Save window in Microsoft Word. Alternatively, click the File tab on the Ribbon and select Save As to launch the Save As window.

Step 5: Enter a name for your file in the File name field.

Step 6: Click the Save as type drop-down and select .html.

Step 7: Click Save to create the .html file.

Step 8: Open your Windows File Library and navigate to the location with your saved .html file.

Step 9: Right-click on the file and click Open with Notepad.

Step 10: Copy the content of the HTML file and paste it into a Word document.

Step 11: Click the Ctrl + H keys on your keyboard to launch the Find and Replace dialog box. Alternatively, click the Replace button on the Home tab.

Step 12: In the Find what field, enter the unwanted code surrounding the hyperlink.

Step 13: In the Replace with field, leave a blank space and click Replace All. This ensures all unwanted code are replaced with a blank space.

Step 14: Repeat steps 12 and 13 to remove all unwanted code until only the hyperlinks are left.

Step 15: Copy the hyperlinks and transfer them to a blank column in Excel.

This method is only advisable when dealing with a moderate data set. Additionally, you should only use this method when you’re not pressed for time to extract the hyperlinks.

How to Extract a URL From Hyperlinks Using VBA Code

With VBA, you can create a code to help you extract the hyperlinks within a short amount of time. This is particularly useful when working with a large data set. Here’s how to do so:

Step 1: From your PC’s Start menu or Taskbar, click the Excel workbook with the hyperlinks to open it.

Step 2: Click the Alt + F11 keys on your keyboard to open the VBA window.

Step 3: On the VBA window, click the Insert tab.

Step 4: Select Module from the options to launch the Module window.

Step 5: Copy and paste the following code into the Module window.

Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub

Step 6: Click the Run tab and select Run Sub/UserForm.

Step 7: Close the VBA window by clicking the Alt + Q keys. Your extracted hyperlinks should show up in a new column.

Fixing Non-Functioning Hyperlinks in Excel

Hyperlinks in Excel are an easy way to refer to relevant information in a worksheet or move from one worksheet to another. Hyperlinking not only saves time but also helps in organizing data efficiently. If the hyperlinks in your Excel workbook do not work, try checking the link path, repairing the workbook, or running the Office repair tool to fix it.