3

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.

2

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.

  • Yeah, this definitely seems to be the easiest workaround short of writing a custom function. It's a shame sheets doesn't support an evaluate() function, that could've come in handy here. Thanks! – Puneet Mulchandani Apr 17 '18 at 19:01
4

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.

0

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.

0

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.

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.