How To Get A Hyperlink From A URL – Formula To Extract URL From Hyperlink In Google Sheets

by

Anchor text is more recognizable to most people than URLs. Users, on the other hand, would want to know the URL address for a number of reasons. In Google Sheets, URLs may be readily extracted from a given text. Google Sheets users may extract hyperlinks from the text in a number of ways. If you are a person who wants to know the formulas to extract URLs from the given text, then this page is for you. In this article, we will show you all of the methods for extracting the URL from hyperlinks using Google Sheets Tips. Continue reading to learn more.

Table of Contents

One of the best ways to extract the URL from hyperlinks in a Google Spreadsheet is to use the hover and copy method. The detailed steps on how to do this in Google Sheets are given below:

  • 1st Step: Open the Google Spreadsheet.
  • 2nd Step: Now on the homepage, hover over the cell where the hyperlink is given.
  • 3rd Step: In the pop-up, click on the “Copy URL” icon.
  • 4th Step: Now move to the other cell and paste the URL by using the keyboard shortcut “Ctrl+V“.

extract-hyperlink-url

Alternatively, you can right-click and choose “Paste” to paste the URL in the spreadsheet.

Another method to extract the URL from Google Sheets is to use the Copy and Edit method. The detailed steps on how to do this in Google Sheets are given below:

  • 1st Step: Launch the Google Spreadsheet on your device.
  • 2nd Step: Now on the homepage, hover your mouse over the cell where the hyperlink is present.
  • 3rd Step: A small pop-up will appear on the screen. Now click on the “Edit Link” icon from the list of options.

extract-hyperlink-url

  • 4th Step: Now a small window will open on the screen. Under the “Apply” section, copy the link.

extract-hyperlink-url

  • 5th Step: Paste the copied link in the required cell using the keyboard shortcut “Ctrl+V“.

 

Now you will see the extracted URL being pasted in the necessary place. To access the Edit Link window, one can also use one of the following methods:

  1. Hover your mouse over the cell where the hyperlink is presented and use the keyboard shortcut “Ctrl+K“.
  2. Select the hyperlink and right-click anywhere on the screen and choose “Edit Link” from the drop-down menu.

One can also use the Apps Script in Google Spreadsheet to extract the hyperlink in the spreadsheet. The instructions for using Apps Script in Google Spreadsheet are provided below.

  • 1st Step: First, launch Google Sheets on your device.
  • 2nd Step: Now on the homepage, click on the “Extensions” tab and choose “Apps Scripts” from the drop down menu.
  • 3rd Step: Copy and paste the following code and click on the “Run” button.
function GetURL(input) {
  var myFormula = SpreadsheetApp.getActiveRange().getFormula();
  var myAddress = myFormula.replace('=GetURL(','').replace(')','');
  var myRange = SpreadsheetApp.getActiveSheet().getRange(myAddress);
  return myRange.getRichTextValue().getLinkUrl()
 }

extract-hyperlink-url

 

  • 4th Step: Once the execution is completed, come back to the spreadsheet.
  • 5th Step: Now refresh the spreadsheet and move to the cell where you want to apply the formula to extract the hyperlinks.
  • 6th Step: Enter the formula “=GetURL (A1)“.
  • 7th Step: Press the “Return” key and you will find the results as shown in the image given below.

extract-hyperlink-url

Formula Explanation: In the Apps Script, we have created a custom function named “GetURL” using Java Script that is capable of extracting the URL from the hyperlinks.

Though there are hundreds of built-in functions in Google Sheets, the URL extraction function is missing. Thus to extract the URL in the spreadsheet, one can simply use the methods provided here or can also create a custom function that is capable of extracting the URLs in the spreadsheet.

Leave a Comment