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..

protected by Community Jun 12 '18 at 22:00

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?