Pinterest Pixel

The Easiest Ways to Extract Data from Hyperlinks in Excel

Sometimes, you might have URLs or hyperlinks within your Excel spreadsheet and need to extract the link... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

The Easiest Ways to Extract Data from Hyperlinks in Excel | MyExcelOnline The Easiest Ways to Extract Data from Hyperlinks in Excel | MyExcelOnline

Sometimes, you might have URLs or hyperlinks within your Excel spreadsheet and need to extract the link data for accessibility. In this article, we’ll explore five methods to extract data from Hyperlinks in Excel, including plain text URLs –

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.

The Easiest Ways to Extract Data from Hyperlinks in Excel

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

The Easiest Ways to Extract Data from Hyperlinks in Excel

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.

The Easiest Ways to Extract Data from Hyperlinks in Excel

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:

See also  Easy Guide: Mute on Microsoft Teams for Focused Meetings

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.

The Easiest Ways to Extract Data from Hyperlinks in Excel

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.

The Easiest Ways to Extract Data from Hyperlinks in Excel

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

Right-click on the cell that contains the hyperlink.

The Easiest Ways to Extract Data from Hyperlinks in Excel

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.

The Easiest Ways to Extract Data from Hyperlinks in Excel

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).

See also  Reverse Text Function Using Macros In Excel

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

The Easiest Ways to Extract Data from Hyperlinks in Excel

The Easiest Ways to Extract Data from Hyperlinks in Excel

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.

The Easiest Ways to Extract Data from Hyperlinks in Excel

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.

The Easiest Ways to Extract Data from Hyperlinks in Excel

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

See also  Named Ranges with Vlookup Formula

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.

The Easiest Ways to Extract Data from Hyperlinks in Excel

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.

The Easiest Ways to Extract Data from Hyperlinks in Excel
Exit the VBA by pressing Alt+Q.

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

The Easiest Ways to Extract Data from Hyperlinks in Excel

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

The Easiest Ways to Extract Data from Hyperlinks in Excel

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.

The Easiest Ways to Extract Data from Hyperlinks in Excel

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!

See also  INDEX-MATCH Maximum Sales

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

If you like this Excel tip, please share it
The Easiest Ways to Extract Data from Hyperlinks in Excel | MyExcelOnline The Easiest Ways to Extract Data from Hyperlinks in Excel | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...