Extract URL from hyperlink in Google Sheets where there is no `hyperlink()` function being used

Question

I've got a hyperlink that I've copied from a website and put into a Google Sheet.

If you hover over the hyperlink it shows you it is an active link and you can click it. But if you view the formula in the cell you can't see the =hyperlink() function being used. See screenshot below.

I want to be able to generate a list of raw URLs from these hyperlinks, is there anyway I can do that in Google Sheets ? (FYI I've also tested in Excel which has the same issue of not using the hyperlink() formula.

I've made a test version of the sheet here in case someone wants to try.

Answer 1

That can be done, but only with a script.

The RichTextLinks custom function is one good option. I tested it in a copy of your sample spreadsheet and it worked fine. Here's how to use it:

=RichTextLinks("A1", A1)

The custom function can also fill a whole column in one go, like this:

=RichTextLinks("A2:A", A2:A)

Answer 2

This is an example of a custom formula by @FredericoSchardong found on StackOverflow - How to extract URL from Link in Google Sheets using a formula?

/** 
 * Returns the URL of a hyperlinked cell, if it's entered with control + k. 
 * Author: @Frederico Schardong based on https://support.google.com/docs/thread/28558721?hl=en&msgid=28927581 and https://github.com/andrebradshaw/utilities/blob/master/google_apps/convertHiddenLinks.gs 
 * Supports ranges
 */
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.getRichTextValues();
  var output = [];
  for (var i = 0; i < formulas.length; i++) {
    var row = [];
    for (var j = 0; j < formulas[0].length; j++) {
      row.push(formulas[i][j].getLinkUrl());
    }
    output.push(row);
  }
  return output
}

---

Sample

Answer 3

An answer (as submitted) by @MrG also from StackOverflow How to extract URL from Link in Google Sheets using a formula?

function GETLINK(input){

    return SpreadsheetApp.getActiveSheet().getRange(input).getRichTextValue().getLinkUrl();

}

This solution works, but it not as good as others because of the format of the imput value. For example, instead of entering A1, one must enter "A1". BUT, it does work.

It would be easy to adapt the formula so that the the input range would be simply A1, but that is a task for another day.

---

Sample

Addendum

In the webapps question: How to extract hovered links from a cell in Google Sheets?, on April 22 2021, MrG comments:

If cell A1 has the link you will need to reference it as =GETLINK("A1") That's not going to update dynamically if you have a lot of links so use =GETLINK(cell("Address",A1)) to get around that.