Google Sheets – Paste Hyperlink Formulas and Replace Cell References with Values

google sheets

Consider the following:

A                  | B        | C
------------------ | -------- | -------------------
=HYPERLINK(C2, B2) | Google   | http://google.com
=HYPERLINK(C3, B3) | Facebook | http://facebook.com
=HYPERLINK(C4, B4) | Uber     | http://uber.com
...

I wish to remove columns B & C after I have done creating the hyperlinks using the formulae. But I get reference errors as the column refers to the data in B & C.

So, I copied column A & tried to do Paste Special > Paste Values Only (and did error & trial with every other option in there), but none of it removed the the references in the formula.

Is there any way to have hyperlinks without the references?

Best Answer

I describe two approaches.

Rich text

One way is to create a column of hyperlinks in Google Docs (or any other editor that supports hyperlinks) and then copy-paste it into a spreadsheet. The result will be hyperlinks which are rich text, not formulas.

Caveat: at present, there is no way to extract the URL from such hyperlinks with spreadsheet formulas or Apps Script. So if in the future you or someone else decides they need the list of these URLs, there'll be a problem.

Including data in formulas

Using Apps Script, one can fill column A with formulas such as =hyperlink("http://google.com", "Google") using the data from B,C. After running the script and verifying the result, B and C can be deleted.

function inlineHyperlinks() {
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var range = sheet.getRange("B2:C");
  var values = range.getValues();
  var formulas = [];
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] && values[i][1]) {
      formulas.push(['=hyperlink("'+ values[i][1] +'", "'+ values[i][0] +'")']);
    }
    else {
      formulas.push(['']);
    }
  }
  sheet.getRange(2, 1, formulas.length, 1).setFormulas(formulas);
}