3

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 created the hyperlinks using the formulae, but then I get #REF! reference errors because the formulae rely on the data in those columns.

I tried copying column A and then using Paste Special > Paste Values Only but it didn't replace the cell references by their values. Nor did any other Paste Special option work any better.

Is there any way to copy the hyperlinks with the cell references replaced by their values?

3 Answers 3

3

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.

0
2

I had the same issue and found a solution.

  1. Copy the column from your spreadsheet.
  2. Paste the column into a Google Docs document (not Google Sheets) without linking.
  3. Copy the column from the document and paste it back into your spreadsheet.

Weird, but it works.

1
  • Great simple solution 👍 Worth noting that the formula is removed with your approach and replaced by a standard Docs link. This may not be an issue for you or the OP. If it is, or you have the same issue with functions that are not hyperlinks, see my aporoach.
    – Blindspots
    Oct 11, 2023 at 19:51
0

This solution maintains the HYPERLINK formula in the final results.

If you are simply interested in returning a clickable link, user69420's answer is probably the best approach to achieve that goal.

Formula in A1

=ARRAYFORMULA(
   IF(LEN(C:C),  
     "=HYPERLINK("& CHAR(34)&
     C:C& CHAR(34)& ","& CHAR(34)&
     B:B& CHAR(34)& ")",))  
  • Formula will return results for all rows that have a value in column C.

Instructions

  1. Clear column A and put the new formula in A1
  2. Copy column A and Paste back over column A using
    Paste Special > Paste Values Only
    • PC:  Ctrl+Shift+V
    • MAC: +Shift+V
  3. Format column A as Number
  4. [OPTIONAL] Re-format column A as Automatic

Note: If your data starts in Row 2, put the formula in A2 and make the formula's column references C2:C and B2:B

Explanation

  • You can use "Paste Values Only" but only if the formula generates values that match what you want to paste.
  • The new formula generates values that match what the HYPERLINK formula would look like if you included the actual values instead of references to cells containing those values.
  • When the values are then copied and pasted they are valid formulas
  • The number format is toggled so Sheets doesn't interpret the final values as text strings.

Comparing Pasting Your Formula Results with the New One

Formula   After Step 3 After Step 1
Yours Google Google
New One Google =HYPERLINK("http:// google .com",  "Google")

Single-Row Formula

This formula forms the basis of the ARRAYFORMULA above and would need to be copied to every relevant row in column A which is why an ARRAYFORMULA is preferred.

="=HYPERLINK("& CHAR(34)&
  C1& CHAR(34)& ","& CHAR(34)&
  B1& CHAR(34)& ")",)) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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