I bet you tried searching for a Google Sheets custom function that returns the URL or Link from a rich text cell. The top search result looks like it works. However it requires the parent cell to use =hyperlink(). This won’t work for us. If you push a little deeper, you’ll find user Will in RVA post a solution. This works but is cumbersome because it requires you to have a helper cell.

I took the best of both worlds and made a function that does what you really want it to do. It extracts the link URL directly from a rich text cell in Google Sheets. Go to Tools > Script editor and copy this script. Save and Enjoy!

/** 
 * Returns the URL from a rich text cell.
 * @param {A1}  reference Cell reference
 * @customfunction
 */
function linkyURL(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 cell = range; 
  var cellValue = cell.getRichTextValue();
  var cellText = cell.getValue();
  var theThing = cellValue.getLinkUrl();
  if (theThing == null){
    return cellText;
  } else {
    return theThing; 
  }
}

If this helps you out please share and let me know.