Technology

The Power of Hyperlinks: Unlocking Excel’s Secrets for Seamless Data Extraction

Excel is undoubtedly a familiar spreadsheet software to all of us. Sometimes, you may need to extract a link from an Excel spreadsheet but unsure of how to do it. Follow the article below to learn how to extract a link in Excel!

Administrator

25 December, 2023

244

1. How to Extract Links from an Excel File

Use the Edit Hyperlink tool to extract links in Excel.

You can only extract one link at a time using this method. If you have multiple hyperlinks, you’ll need to repeat the process for each extraction.

Step 1: Right-click on the cell with the Hyperlink and select Edit Hyperlink.

Guide to Extracting Links in Excel Quickly and Simply

Step 2: Right-click on the cell with the Hyperlink and select Edit Hyperlink again.

Step 3: Close the dialog box and paste the link wherever you need it.

2. How to Extract Links from an Excel File Using VBA Code

When dealing with a large number of hyperlinks, the manual method described above can be time-consuming. The following VBA code will help you extract multiple links from your Excel file simultaneously.

Step 1: In Excel, press and hold ALT+F11 to open the Microsoft Visual Basic for Applications window.

Step 2: Choose Insert > Module, and paste the following code:

Sub ExtractHyperlinks() 'Updateby20140318 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "Excel Online Learning" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) For Each Rng In WorkRng If Rng.Hyperlinks.Count > 0 Then Rng.Value = Rng.Hyperlinks.Item(1).Address End If Next End Sub

Step 3: Press F5 to run the code. A dialog box will appear, asking you to select the hyperlinks you want to extract, as shown below:

Step 4: Click OK, and the links will be converted into actual addresses.

3. How to Extract Links in Excel Using a Custom Function

In addition to the methods mentioned above, you can also extract links in Excel using a custom function. Here are the steps to follow:

Step 1: Press and hold ALT+F11 to open the Microsoft Visual Basic for Applications window.

Step 2: Choose Insert > Module, and then paste the following code:

Function GetURL(pWorkRng As Range) As String 'Updateby20140520 GetURL = pWorkRng.Hyperlinks(1).Address End Function

Step 3: Save the code and close the window. Select an empty cell and enter the following formula: =GetURL(A2) (where A2 is the cell containing the hyperlink), and press Enter. You should immediately see the extracted link address.

4. How to Extract Links in an Excel File Using Find and Replace

This method will help you find a list of cells that contain links. You can then click on the cell address to open the hyperlink.

Step 1: Press Ctrl + F to open the Find and Replace dialog box. Then, select the Options tab.

Press Ctrl + F to open the Find and Replace dialog box and select the Options tab.

Step 2: Choose Format > Choose Format from Cell…

Step 3: Click on the cell with the inserted link. The Format section will display a Preview.

Click on the cell with the inserted link. The Preview will appear in the Format section.

Step 4: Click Find All to display all cells with inserted links.

Conclusion

The above guide provides four detailed methods to extract links in Excel, with simple and easy-to-follow steps. If you have any questions or need further clarification, feel free to leave a comment below.

Additionally, FPT Shop offers a wide range of tech products at great prices, including tablets, phones, laptops, and accessories. Drop by to find yourself a great deal on a high-performance laptop that will enhance your productivity in work and study.

Laptop

You may also be interested in:

Guide: 4 Ways to Transfer Data from One Table to Another in Excel, Explained in Detail
Understanding the ‘Different’ Symbol in Excel: How to Write and Use It Effectively

Frequently asked questions

You can extract links in Excel using the ‘Edit Hyperlink’ tool, VBA code, a custom function, or the ‘Find and Replace’ feature. For the ‘Edit Hyperlink’ method, right-click on the cell with the hyperlink and select ‘Edit Hyperlink’. Repeat this process for each link. If you have VBA code skills, you can use the provided code to extract multiple links simultaneously. Alternatively, use Alt+F11 to open the Microsoft Visual Basic for Applications window, insert a new module, and paste the custom function code. Finally, the ‘Find and Replace’ method will help you find and list all cells with links, which you can then open individually.

The VBA code to extract multiple hyperlinks is as follows:
Sub ExtractHyperlinks() ‘Updateby20140318 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = “Excel Online Learning” Set WorkRng = Application.Selection Set WorkRng = Application.InputBox(“Range”, xTitleId, WorkRng.Address, Type:=8) For Each Rng In WorkRng If Rng.Hyperlinks.Count > 0 Then Rng.Value = Rng.Hyperlinks.Item(1).Address End If Next End Sub

To use a custom function, open the Microsoft Visual Basic for Applications window (Alt+F11), choose ‘Insert > Module’, and paste the custom function code. Save and close the window. In your Excel sheet, select an empty cell and enter the formula: =GetURL(A2), where A2 is the cell with the hyperlink. Press Enter, and the extracted link address will appear.

Yes, use the ‘Find and Replace’ feature. Press Ctrl + F to open the dialog box, select the ‘Options’ tab, choose ‘Format’, and then ‘Choose Format from Cell’. Click on a cell with a hyperlink to generate a preview in the ‘Format’ section. Finally, click ‘Find All’ to list all cells with inserted links.