- 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 HyperlinkFor Each HL In ActiveSheet.HyperlinksHL.Range.Offset(0, 1).Value = HL.AddressNextEnd 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.
