17

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

  • 1
    Here is a script that can do it: productforums.google.com/forum/#!topic/docs/ymxKs_QVEbs – Yisroel Tech Feb 5 '16 at 3:01
  • 3
    Note to visitors: if you are looking for a way to extract the URL from a formatted link that is not a =hyperlink() (something that was pasted into a sheet), then sorry: there isn't one. It's best not to paste rich text into spreadsheets to begin with. – user79865 Apr 11 '16 at 23:16
  • Duplicate of stackoverflow.com/questions/28474308/… ? – sampablokuper Jun 30 '16 at 3:23
  • 1
    note to visitors 2: you can get them both if you download the spreadsheet in html. or rather, they are easily extractable from the html....not ideal, but it is a way. – albert Sep 24 '16 at 18:00
10

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

  1. 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.
  2. 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
}
  • works brilliantly, though a little slow. – Dannid Feb 26 '16 at 23:32
  • This technically works, but I'm wondering if it's possible to create a new hyperlink based on linkURL() result. e.g. =HYPERLINK(linkURL(C2),"new label") doesn't seem to work for me. – skube Apr 8 '16 at 17:52
  • 1
    @skube That's a side effect of how I coded the function: it can only be used on its own, not together with others. You can still make a new hyperlink as =hyperlink(D2, "new label") where D2 has the linkURL formula. Alternatively, use Rubén's custom function. – user79865 Apr 8 '16 at 17:58
3

Short answer

Use a custom function to get the quoted string inside a cell formula.

Code

The external post shared in the comment by Yisroel Tech includes a script that replaces each formula in the active range by the first quoted string in the corresponding formula. The following is an adaptation as custom function of that script.

/** 
 * Extracts the first text string in double quotes in the formula
 * of the referred cell
 * @param {"A1"}  address Cell address.
 * @customfunction
 */
function FirstQuotedTextStringInFormula(address) {
  // Checks if the cell address contains a formula, and if so, returns the first
  // text  string in double quotes in the formula.
  // Adapted from https://productforums.google.com/d/msg/docs/ymxKs_QVEbs/pSYrElA0yBQJ

  // These regular expressions match the __"__ prefix and the
  // __"__ suffix. The search is case-insensitive ("i").
  // The backslash has to be doubled so it reaches RegExp correctly.
  // https://developer.mozilla.org/en-US/docs/JavaScript/Reference/Global_Objects/RegExp

  if(address && typeof(address) == 'string'){

    var prefix = '\\"';
    var suffix = '\\"';
    var prefixToSearchFor = new RegExp(prefix, "i");
    var suffixToSearchFor = new RegExp(suffix, "i");
    var prefixLength = 1; // counting just the double quote character (")

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var cell, cellValue, cellFormula, prefixFoundAt, suffixFoundAt, extractedTextString;

    cell = ss.getRange(address);
    cellFormula = cell.getFormula();

    // only proceed if the cell contains a formula
    // if the leftmost character is "=", it contains a formula
    // otherwise, the cell contains a constant and is ignored
    // does not work correctly with cells that start with '=
    if (cellFormula[0] == "=") {

      // find the prefix
      prefixFoundAt = cellFormula.search(prefixToSearchFor);
      if (prefixFoundAt >= 0) { // yes, this cell contains the prefix
        // remove everything up to and including the prefix
        extractedTextString = cellFormula.slice(prefixFoundAt + prefixLength);
        // find the suffix
        suffixFoundAt = extractedTextString.search(suffixToSearchFor);
        if (suffixFoundAt >= 0) { // yes, this cell contains the suffix
          // remove all text from and including the suffix
          extractedTextString = extractedTextString.slice(0, suffixFoundAt).trim();

          // store the plain hyperlink string in the cell, replacing the formula
          //cell.setValue(extractedTextString);
          return extractedTextString;
        }
      }
    } else {
      throw new Error('The cell in ' + address + ' does not contain a formula');
    }
  } else {
    throw new Error('The address must be a cell address');
  }
}
  • 1
    This function is better for me, because it can be used inside other expressions. By the way it uses {"A1"} notation to address the cell. – vatavale Aug 20 '17 at 20:26
2

Assuming the cell has the hyperlink funciton;

Just find and replace =hyperlink to "hyperlink" or "xyz"

Then you just have to do some data cleaning to separate them. Try using the split text to columns or the =split function. Both would use , as a delimiter.

Again replace the " [double quotes] with [nothing]

Seems way simpler this way..