1

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?

2

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);
}

Side-stepping the issue: hide the columns

This is the easiest: just hide the columns B,C and forget about them.

|improve this answer|||||
  • Excuse my ignorance; do you mean to say that I can run such arbitrary code against one of my spread sheets using App Script? – Jikku Jose Jun 27 '16 at 6:25
  • Yes. In the spreadsheet menu, see Tools > Script Editor: this opens editor window where code can be entered and executed. – user79865 Jun 27 '16 at 6:26
  • On a different note: I can't digest that someone didn't think of button for such tasks, say something like select all required cells & click 'Dereference'. I am guessing it would also avoid the paste special hack most use for such things. – Jikku Jose Jun 27 '16 at 6:27
  • Thanks it worked, though I didn't fully grasp the code. It would awesome if you can explain what this does: sheet.getRange("B2:C") – Jikku Jose Jun 27 '16 at 7:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.