Discover the simplest techniques to extract URLs from hyperlinks in Excel! Dive into the details below and enhance your data management skills.

Download the Excel Workbook to practice these methods:

Download excel workbookGetURLfromHyperlink.xlsm

Method 1: Manual Extraction
The most direct approach to retrieve a URL from an Excel cell is through manual copying.

Begin by opening your Excel document.

Select the cell with the hyperlink you wish to extract.

Clicking the hyperlink will launch the associated webpage or resource in your default browser.

Copy the URL displayed in your browser's address bar.

This technique is user-friendly and best suited for occasional use, particularly when extracting just a few URLs.

Method 2: Accessing Hyperlink Data
Excel enables you to insert hyperlinks into cells, and extracting the link data is straightforward:

First, select the cell with the hyperlink.

Observe the Formula Bar; it will show the hyperlink's address. Copy the link by highlighting it and pressing CTRL C.

Method 3: Using Right-Click
Another method to extract the hyperlink URL from a cell involves right-clicking:

Open your Excel file.

Identify the cell containing the hyperlink you want to extract the URL from.

Right-click on the cell with the hyperlink.

From the context menu, select:

Open Hyperlink to open the link in a browser, or

Edit Hyperlink to view and copy the URL.

To copy the URL without opening it, highlight it, right-click, and select Copy Hyperlink.

You can then paste the URL anywhere needed (e.g., another cell, text document, or browser address bar) using Ctrl V or Command V.

Method 4: Extracting Text URLs
For plain text URLs within a cell, Excel's text functions can help extract them:

Assume the plain text URL is in cell A7.

To retrieve the URL directly, use the formula:

=A7

Method 5: Automating with VBA Macros
For extracting link data from multiple cells, VBA macros offer an automated solution.

Access the VBA editor by pressing Alt F11.

Add a new module by navigating to Insert then Module.

Copy and Paste the following code:

Sub ExtractHL()

Dim HL As Hyperlink

For Each HL In ActiveSheet.Hyperlinks

HL.Range.Offset(0, 1).Value = HL.Address

Next

End Sub

Press F5 and select Run.
Exit VBA by pressing Alt Q.

Alternative: For adding more hyperlinks and storing the formula on the sheet:

Enter VBA by pressing Alt F11.

Navigate to the VBA editor or press Alt F11.

Insert a new module via Insert then Module.

Implement this custom Excel function: =URL(cell)

Copy and Paste the following code:

Function URL(Hyperlink As Range)
URL = Hyperlink.Hyperlinks(1).Address
End Function

Press F5 and select Run.

Exit VBA by pressing Alt Q.

Now apply the custom formula =URL(A2) to extract the URL from the hyperlink.

Extend the formula downward to apply it to other links.

Modify and use the extracted data as required.

Note: VBA codes cannot be saved in the .xls format. Use the .xlsm format to save the workbook with VBA code.

Conclusion:
Leveraging hyperlink data in Excel is key for effective data management and automation. Whether using Excel's built-in HYPERLINK function or VBA scripting, extracting hyperlink information is straightforward. Employ these techniques next time you need to pull data from hyperlinks in Excel!

Click here to explore Microsoft’s guide on Using the HYPERLINK Function!

The above is the detailed content of The Easiest Ways to Extract Data from Hyperlinks in Excel. For more information, please follow other related articles on the PHP Chinese website!