Google Sheets – How to Extract Link Text and URL from Hyperlinked Cell

google sheetsgoogle-apps-scriptlinks

Suppose I have a hyperlink in cell A1: =hyperlink("stackexchange.com", "Stack Exchange")

Elsewhere in the sheet, I'd like to have formulas that get the link text and URL from A1, separately. I found a way to get just the link text:

=""&A1

(concatenation with empty string). This returns "Stack Exchange", unlinked.

How to get the URL (stackexchange.com)?

Best Answer

After seeing Rubén's answer I decided to write a different custom function for this task, with the following features:

The parameter is provided as a range, not as a string: that is, =linkURL(C2) instead of =linkURL("C2"). This is consistent with how parameters usually work, and makes the references more robust: they will be maintained if someone adds a new row on top.
Arrays are supported: =linkURL(B2:D5) returns the URLs of all hyperlink commands found in this range (and blank cells for other places).

To achieve 1, I do not use the argument passed by the sheet (which would be the text content of the target cell), but instead parse the formula =linkURL(...) itself and extract range notation from there.

/** 
 * 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
}

Update

If you really want to get the result in A1, then you need to use a script.

Code

// global
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menu = [{name: "create URL", functionName: "createURL"}];
  ss.addMenu("URL", menu);
}

function onEdit(e) {
  var activeRange = e.source.getActiveRange();

  if(activeRange.getColumn() == 1) { 
    if(e.value != "") { 
      activeRange.setValue('=HYPERLINK("http://www.example.com/id/'+e.value+'","'+e.value+'")');
    }
  }
}

function createURL() {
  var aCell = ss.getActiveCell(), value = aCell.getValue();
  aCell.setValue('=HYPERLINK("http://www.example.com/id/'+value+'","'+value+'")');  
}

Explained:

The e.value will retrieve the cells value (only applicable a cell). The setValue() will add the concatenated string into the getActiveRange(). All is only executed when e.value contains something and the active range is in column A.

I've created an extra menu option as well, to be able access the script this way.

Example:

I've created an example file for you: onEdit URL builder
Add this script via Tools>Script editor, into the script editor. Press the "bug" button and you can use the script.

Google Sheets – Create Link in Cell Using URL from Another Cell

You were so close !!!!!!!!

Use the following formula.

Formula

=ARRAYFORMULA(
   HYPERLINK(
     FILTER(Data!D2:D40,Data!B2:B40=D2),
     FILTER(Data!A2:A40,Data!B2:B40=D2)
   )
 )

Explained

The ARRAYFORMULA will take on the ranges, set in the filter, and return the matches.