21

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

5
  • 1
    Here is a script that can do it: productforums.google.com/forum/#!topic/docs/ymxKs_QVEbs Feb 5, 2016 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, 2016 at 23:16
  • Duplicate of stackoverflow.com/questions/28474308/… ?
    – user6757
    Jun 30, 2016 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, 2016 at 18:00
  • 1
    =REGEXEXTRACT(FORMULATEXT(A1), "^=HYPERLINK(""([^""]+)")
    – Mark Doyle
    Jul 7, 2021 at 9:51

4 Answers 4

12

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
}
3
  • works brilliantly, though a little slow.
    – Dannid
    Feb 26, 2016 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, 2016 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, 2016 at 17:58
4

Short answer

Use a custom function.

NOTE

Nowadays Google Sheets have two ways to have links in a cell:

  • by using the HYPERLINK function and
  • by using rich text feature for cell content formatting.

This answer focus in cells using the HYPERLINK function, more specifically, includes code to get the first quoted string inside a cell formula.

Code for cells using the HYPERLINK function

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');
  }
}

Related

1
  • 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, 2017 at 20:26
3

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

1

The scripts from the other answers do not work when the links are not stored as formulas, they are stored as RichTextValue.

This is the a way to get a link from a cell not having a formula in Google Sheets:

function getCellLink(address) {

  if(!(address && typeof(address) == 'string'))
    throw new Error('The 1st param must be a cell address as a string.');

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getRange(address);
  var url = cell.getRichTextValue().getLinkUrl()

  if (!url) 
    throw new Error('The cell in ' + address + ' does not contain a link.');
  
  return url
}
5
  • Have you tested this on cells having multiple links?
    – Rubén
    Aug 27 at 15:48
  • Rubén, are you sure that the time information should go away? This did not work few years ago, and won't work in few years. While the other solutions did work, but do not work anymore. Removing the time info causes unnecessary confusion. Yes, I have tested it on an entire spreadsheet. Aug 30 at 8:41
  • This works in August 2022. Aug 30 at 8:42
  • It looks that you are missing that the question is about getting the URL from a formula using the HYPERLINK function. This functions still works. If you and others aren't using the HYPERLINK function, that is fine, but that doesn't mean that the HYPERLINK function was retired.
    – Rubén
    Aug 30 at 14:28
  • Hmm, good point :-) In my case, pasting from a sheet with hyperlink()s created this richtext. Maybe just a corner case. I will reword or delete. Thanks for figuring out! Aug 31 at 17:34