{"id":31621,"date":"2023-10-25T22:07:58","date_gmt":"2023-10-25T20:07:58","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=31621"},"modified":"2024-04-06T19:47:47","modified_gmt":"2024-04-06T17:47:47","slug":"extract-data-from-hyperlinks-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/extract-data-from-hyperlinks-in-excel\/","title":{"rendered":"The Easiest Ways to Extract Data from Hyperlinks in Excel"},"content":{"rendered":"

\"The<\/a><\/p>\n

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

Click here<\/a> to learn more about Hyperlinks in Excel!<\/p>\n

Let\u2019s explore these methods!<\/p>\n

Download the Excel Workbook below to follow along and understand how to use The Easiest Ways to Extract Data from Hyperlinks in Excel \u2013<\/h4>\n

Download excel workbook<\/span>GetURLfromHyperlink.xlsm<\/span><\/a><\/p>\n

Method 1: Manual Copy<\/span><\/strong>
\nOne of the most straightforward ways to extract a URL from a cell in Excel is through a manual copy-and-paste method.<\/p>\n

Open your Excel file.<\/p>\n

\"The<\/a><\/p>\n

Click on the cell containing the hyperlink<\/strong> that you want to extract.<\/p>\n

\"The<\/a><\/p>\n

Upon clicking the hyperlink, the linked webpage or resource will open in your default web browser.<\/p>\n

Copy the URL<\/strong> from your browser’s address bar.<\/p>\n

\"The<\/a><\/p>\n

This method is simple and ideal for occasional use, especially if you only need to extract a few URLs.<\/p>\n

Method 2: Extracting Hyperlink Data<\/span><\/strong>
\nExcel allows you to insert hyperlinks into cells, and you can easily extract the link data from these hyperlinks. Here’s how:<\/p>\n

Start by selecting the cell<\/strong> that contains the hyperlink.<\/p>\n

Look at the Formula<\/a> Bar<\/strong>. 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<\/strong>.<\/p>\n

\"The<\/a><\/p>\n

Method 3: Right-Click<\/span><\/strong>
\nAnother way to extract the hyperlink URL from a cell is by using the right-click method. Here’s how you can do it:<\/p>\n

Open your Excel spreadsheet.<\/p>\n

\"The<\/a><\/p>\n

Locate the cell that contains the hyperlink you want to extract the URL from.<\/p>\n

Right-click<\/strong> on the cell that contains the hyperlink.<\/p>\n

\"The<\/a><\/p>\n

In the context menu, choose:<\/p>\n

Open Hyperlink<\/strong>, if you want to open the hyperlink in a web browser, choose<\/p>\n

Edit Hyperlink<\/strong>, if you want to edit the hyperlink. From there, you can see and copy the URL.<\/p>\n

If you want to copy the hyperlink URL without opening it, highlight the URL, right-click, and choose Copy Hyperlink<\/strong>.<\/p>\n

\"The<\/a><\/p>\n

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).<\/p>\n

Method 4: Extracting Plain Text URLs<\/span><\/strong>
\nIf the link is a plain text URL within a cell, you can use Excel’s text functions to extract it. Here’s an example:<\/p>\n

Let\u2019s say the plain text URL is in cell A7<\/strong>.<\/p>\n

To extract the URL as is, you can use the formula:<\/p>\n

=A7<\/strong><\/p>\n

\"The<\/a><\/p>\n

\"The<\/a><\/p>\n

Method 5: Using VBA Macros<\/span><\/strong>
\nIf you need to extract link data from multiple cells, you can automate the process using VBA<\/a> (Visual Basic for Applications).<\/p>\n

Click here<\/a> to learn more about how to Master VBA Loop in Excel!<\/p>\n

Here’s a simple VBA macro<\/a> to extract hyperlinks from a range of cells:<\/p>\n

Go to the VBA Menu or press Alt+F11<\/strong>.<\/p>\n

\"The<\/a><\/p>\n

Insert a new module by going to Insert<\/strong> then Module<\/strong>.<\/p>\n

Copy and Paste<\/strong> this function:<\/p>\n

Sub ExtractHL()\u2028<\/em><\/strong><\/p>\n

Dim HL As Hyperlink\u2028<\/em><\/strong><\/p>\n

For Each HL In ActiveSheet.Hyperlinks\u2028<\/em><\/strong><\/p>\n

HL.Range.Offset(0, 1).Value = HL.Address\u2028<\/em><\/strong><\/p>\n

Next\u2028<\/em><\/strong><\/p>\n

End Sub<\/em><\/strong><\/p>\n

Press F5<\/strong> and click Run<\/strong>.
\nExit the VBA by pressing Alt+Q<\/strong>.<\/p>\n

\"The<\/a><\/p>\n

Option 2<\/strong>: If you plan to add more hyperlinks to the spreadsheet and need to store the formula on the sheet<\/p>\n

Get into VBA (Press Alt+F11<\/strong>)<\/p>\n

Go to the VBA Menu or press Alt+F11<\/strong>.<\/p>\n

Insert a new module by going to Insert<\/strong> then Module<\/strong>.<\/p>\n

\"The<\/a><\/p>\n

Use this syntax for this custom Excel function: =URL(cell)<\/strong><\/p>\n

Copy and Paste<\/strong> this function:<\/p>\n

Function URL(Hyperlink As Range)<\/em><\/strong>
\nURL = Hyperlink.Hyperlinks(1) .Address<\/em><\/strong>
\nEnd Function<\/em><\/strong><\/p>\n

Press F5<\/strong> and click Run<\/strong>.<\/p>\n

\"The<\/a>
\nExit the VBA by pressing Alt+Q<\/strong>.<\/p>\n

Now you can use the custom formula =URL(A2)<\/strong> to extract the url from the hyperlink.<\/p>\n

\"The<\/a><\/p>\n

Drag the formula down<\/strong> to apply it to the rest of the links.<\/p>\n

\"The<\/a><\/p>\n

Extract and transform the data as needed.<\/p>\n

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.<\/em><\/strong><\/p>\n

\"The<\/a><\/p>\n

Conclusion<\/strong>:
\nUtilizing 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<\/a>. Make sure to use these methods the next time you need to extract data from hyperlinks in Excel<\/a>!<\/p>\n

Click here<\/a> to access Microsoft\u2019s tutorial on How to Use the HYPERLINK Function!<\/p>\n","protected":false},"excerpt":{"rendered":"

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\u2019s explore these methods! Download the Excel […]<\/p>\n","protected":false},"author":1,"featured_media":31642,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"The Easiest Ways to Extract Data from Hyperlinks in Excel","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[56,495],"tags":[1419,64,1421,1420],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/31621"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=31621"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/31621\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/31642"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=31621"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=31621"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=31621"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}