Google Sheets – copy and paste a cell's value while preserving a hyperlink

Question

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.

Answer 1

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.

Answer 2

I found a very easy, and very STUPID workaround. You guys ready for this?

Find & Replace: replace http with http (or any other string that's common to all your URLs). Simply the act of going through and doing a replace refreshes all your cells, and re-evaluates them as links.

Make sure you use the Find and Replace... dialog from the edit menu, as Ctrl+F doesn't give the same options.

Answer 3

Sort of a hack, but I found what seems to be a very simple solution:

Copy and paste the cells into a Google Docs file, and then copy them back to the spreadsheet.

Answer 4

Here's another way - use Concatenate instead of Hyperlink formulas.

In your example: A1 = "https://google.com" and B1 = "Google"

You'll need to make three new columns: C, D, and E

C1 = ("
D1 = ","
E1 = ")

F1 = CONCATENATE("=HYPERLINK",C1,A1,D1,B1,E1)

Copy F1 and paste as Values Only

Answer 5

To get pure links from a =hyperlink() formula:

• Copy links produced with =hyperlink() from the Google Sheets source into a Google Docs table;
• Close the Google Sheets source document and reopen it:
  • If you don't, you'll copy back the formula into the Google Sheets source document.
• Copy links back from the Google Docs table into a new column or sheet:
  • No need to close the Google Sheets source if you copy into another Google sheet.

A little bit of ping pong but it works fine.

Answer 6

Copy the hyperlinked cells to a new sheet at value. Example: if A1 = www.google.com, in B1 type: +A1. this will result into showing the B1 cell as www.google.com with a hyperlink. Copy this to a new google document and from there again copy it to cell A1. the content in cell A1 will now show the text www.google.com as a hyperlinked text.

Clear the B1 value.

Answer 7

The solution below will create the hyperlink cell that does not reference your A1 and B1 cells, but it won't show a hyperlink formula in the formula bar.

1. Open a new sheet.
2. Use the IMPORTRANGE function to import the cells with the hyperlinks to a location on the new sheet.
3. Select and copy the imported cells in the new sheet.
4. Paste to a new location on the new sheet or to any other sheet.

If you select one of the pasted cells and click on the hyperlink icon you will see that it has your A1 text ("Google") as the text for the cell and your B1 URL "https://google.com" as the hyperlink. However, only the A1 text (and not the hyperlink formula) will appear in the formula bar.

Answer 8

1. Copy and paste into a Google Docs.
2. Copy from the Google Docs and paste it back into Google Sheets.