How to get url text out of field with HYPERLINK in it

Question

I have a column with hyperlinks formula in it, for example:

=HYPERLINK("http://example.com", "Link")

I want to get additional column, where only urls (as a text) from the first column will be present, that is, in this example:

http://example.com

Is there a function that allows extraction of an url from HYPERLINK? I was thinking also about getting formula text from the first column and cutting it with SPLIT/SUBSTITUTE in the final one, but I'm not sure if I can get one field code into another field.

Answer 1

Try this formulas

A2=index(SPLIT(SUBSTITUTE(FORMULATEXT(A1),"=HYPERLINK(""",""),""","""),1,1)

example

A1=HYPERLINK("http://example.com", "Link")

result is

A2=http://example.com

Answer 2

I just created this script and it worked

function URL(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i);
  
  try {
    var range = sheet.getRange(args[1]).getRichTextValue().getLinkUrl();
  }
  catch(e) {
    throw new Error(args[1] + ' is not a valid range');
  }
  return range;


}