3 Ways to Extract a URL From Hyperlinks in Microsoft Excel

Key Takeaways 

• There are three methods to extract URLs from hyperlinks in Microsoft Excel: editing the Hyperlink directly, using Word processors, and VBA coding.
• VBA code is the most appropriate for large datasets.
• For smaller data, you may simply use the Edit Hyperlink dialog.

Method 1: From the Edit Hyperlink Dialog

The context menu has a series of shortcuts for performing actions. You may use it to open the Edit Hyperlink Dialog and extract your hyperlinks. Here’s how:

Step 1: Launch Excel on your computer and open the document with hyperlinks.

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 to close the Edit Hyperlink dialog box.

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

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.

Method 2: Using Word Processors

You can use Word and an HTML reader like Notepad 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:

Step 1: Launch Excel on your computer and open the document with hyperlinks.

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: Press Ctrl + S 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, click Open with, then select 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: Lastly, copy the hyperlinks and transfer them to a blank column in Excel.

Method 3: 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: Launch Excel on your computer and open the document with hyperlinks.

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: Lastly, close the VBA window by clicking the Alt + Q keys. Your extracted hyperlinks should show up in a new column.