Paste hyperlink formulas but replace cell references with the actual values from the referenced cells

Question

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?

Answer 1

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.

Answer 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.

Answer 3

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)& ")",))