How to extract URL from Link in Google Sheets using a formula?

Question

I have copied from a website a series of hyperlinks and pasted them in a google sheet. The values show up as linked text, not hyperlink formulas, and are still linked correctly. For each row, I'm trying to extract the URL ONLY (not the friendly text) and insert it into the adjacent column. How could this be accomplished using a formula?

For example:

=SOMEFUNCTION(cellThatHoldsLink, returnedURLOnly) 

This and similar scenarios do not apply because the pasted data are not formulas. I will accept a script (GAS) solution, or any solution for that matter, but would prefer if it could be done using a formula. I have found dozens of HYPERLINK manipulation scripts, but nothing on this particular scenario, or even how to access the property that is holding that url. Thanks.

Answer 1

After some update in 2020 all codes I have found on the Internet were broken, so here is my contribution:

/**   * 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 }