Here’s an advanced trick for Google Sheets. If you have a hyperlink in a cell, there’s an easy way to extract the link text and the URL separately.

For example, let’s say you have a cell like this in Sheets with a hyperlink:

And let’s say you want to extract just the URL, or just the link text–not both. Yes, you could go into the formula bar and manually and copy and paste the information, but an easy trick is to use a Google Apps script to do it. If you’re not familiar with the script editor in Google Sheets, don’t be intimidated by it–it’s pretty easy. 

• Head up to Tools > Script editor.
• Copy the following script (courtesy of Stack Exchange), and then hit File > Save.

/** 
 * Returns the URL of a hyperlinked cell, if it's entered with hyperlink command. 
 * Supports ranges
 * @param {A1}  reference Cell reference
 * @customfunction
 */
function linkURL(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=w+((.*))/i);
  try {
    var range = sheet.getRange(args[1]);
  }
  catch(e) {
    throw new Error(args[1] + ' is not a valid range');
  }
  var formulas = range.getFormulas();
  var output = [];
  for (var i = 0; i < formulas.length; i++) {
    var row = [];
    for (var j = 0; j < formulas[0].length; j++) {
      var url = formulas[i][j].match(/=hyperlink("([^"]+)"/i);
      row.push(url ? url[1] : '');
    }
    output.push(row);
  }
  return output
}

• Head up to Run > linkURL to run the script. This will create a new function in Sheets.
• Let’s say cell A1 has the hyperlink in it. Go to any cell and type =linkURL(A1), and then hit Enter.

• And there you go–you get just the link URL from the cell! (You can also use this with a range of cells if you have more than one hyperlink that you want to retrieve.)

If you’re interested, here’s how to achieve the opposite effect (pulling in link text without the URL attached).

• Just type =””& and then the cell (no parenthesis) that you want to retrieve the link text from. Then hit Enter.

• You’ll get the text, but it is no longer an active link.

Click here to watch this video on YouTube.