The Easiest Ways to Extract Data from Hyperlinks in Excel

Click here to learn more about Hyperlinks in Excel!

Let’s explore these methods!

Download the Excel Workbook below to follow along and understand how to use The Easiest Ways to Extract Data from Hyperlinks in Excel –

Download excel workbookGetURLfromHyperlink.xlsm

Method 1: Manual Copy
One of the most straightforward ways to extract a URL from a cell in Excel is through a manual copy-and-paste method.

Open your Excel file.

Click on the cell containing the hyperlink that you want to extract.

Upon clicking the hyperlink, the linked webpage or resource will open in your default web browser.

Copy the URL from your browser’s address bar.

This method is simple and ideal for occasional use, especially if you only need to extract a few URLs.

Method 2: Extracting Hyperlink Data
Excel allows you to insert hyperlinks into cells, and you can easily extract the link data from these hyperlinks. Here’s how:

Start by selecting the cell that contains the hyperlink.

Look at the Formula Bar. You will see the hyperlink’s address displayed there. You can copy the link from the Formula Bar by highlighting the link and pressing CTRL + C.

Method 3: Right-Click
Another way to extract the hyperlink URL from a cell is by using the right-click method. Here’s how you can do it:

Open your Excel spreadsheet.

Locate the cell that contains the hyperlink you want to extract the URL from.

Right-click on the cell that contains the hyperlink.

In the context menu, choose:

Open Hyperlink, if you want to open the hyperlink in a web browser, choose

Edit Hyperlink, if you want to edit the hyperlink. From there, you can see and copy the URL.

If you want to copy the hyperlink URL without opening it, highlight the URL, right-click, and choose Copy Hyperlink.

You can paste the copied URL wherever you need it (e.g., in another cell, in a text document, or in your web browser’s address bar) using the standard paste command (Ctrl+V or Command+V).

Method 4: Extracting Plain Text URLs
If the link is a plain text URL within a cell, you can use Excel’s text functions to extract it. Here’s an example:

Let’s say the plain text URL is in cell A7.

To extract the URL as is, you can use the formula:

=A7

Method 5: Using VBA Macros
If you need to extract link data from multiple cells, you can automate the process using VBA (Visual Basic for Applications).

Click here to learn more about how to Master VBA Loop in Excel!

Here’s a simple VBA macro to extract hyperlinks from a range of cells:

Go to the VBA Menu or press Alt+F11.

Insert a new module by going to Insert then Module.

Copy and Paste this function:

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 click Run.
Exit the VBA by pressing Alt+Q.

Option 2: If you plan to add more hyperlinks to the spreadsheet and need to store the formula on the sheet

Get into VBA (Press Alt+F11)

Go to the VBA Menu or press Alt+F11.

Insert a new module by going to Insert then Module.

Use this syntax for this custom Excel function: =URL(cell)

Copy and Paste this function:

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

Press F5 and click Run.

Exit the VBA by pressing Alt+Q.

Now you can use the custom formula =URL(A2) to extract the url from the hyperlink.

Drag the formula down to apply it to the rest of the links.

Extract and transform the data as needed.

Note: VBA codes cannot be saved in the the default file type .xls. You must use the .xlsm format if you want to save the workbook along with the VBA code.

Conclusion:
Utilizing hyperlink data in Excel can serve as a valuable asset for data management and process automation. Be it through the native HYPERLINK function or VBA scripting, Excel offers a seamless way to extract hyperlink details from your spreadsheets. Make sure to use these methods the next time you need to extract data from hyperlinks in Excel!

Click here to access Microsoft’s tutorial on How to Use the HYPERLINK Function!