Google-sheets – Google Sheets – copy and paste a cell’s value while preserving a hyperlink

google sheets

I'm working with exported data that I've pulled into Google Sheets. I have a column with URLs and a corresponding column with text labels. I can combine the two into a hyperlinked cell using a forumla: =HYPERLINK(A1, B1) where A1 and B1 are cells containing the URL and text label respectively.

Now, I want to copy over the hyperlinked cell into another spreadsheet.

Is there a way to do this that preserves the hyperlink but doesn't reference the original cells?

Example:

  • Let's say A1 = "https://google.com"
  • and B1 = "Google"
  • I set up a formula: =HYPERLINK(A1,B1) which produces a hyperlinked cell showing "Google"
  • I copy this cell and paste it in a different spreadsheet
  • Desired Outcome: the destination cell should be =HYPERLINK("https://google.com", "Google") which produces a hyperlinked cell showing "Google"

Here's what I've tried so far:

  • Simply copying & pasting the cell updates the cell references based on my destination cell
  • Pasting value only just retains the text label but loses the hyperlink
  • The various other "Paste Special" options don't seem to apply (but I tried them out anyway for good measure)
  • Updating the original formula to use $ references (e.g. =HYPERLINK($A$1, $B$1)) allows me to copy and paste the cell correctly within the same sheet. But this fails if I try to copy and paste the cell to a different spreadsheet.

For reference, here's a test spreadsheet where I set up this problem.

Best Answer

I do not see an easy way to resolve this. there is a messy way:

You could make an additional cell with the formula ="HYPERLINK("""& A1 & ""","""&B1&""")" paste it in "as value" to the new sheet and then edit it to add the '=' at the front to make it a formula. including the = in the original construction of the formula results in unintuitive behavior when pasting as value.

Related Topic