3

I have a google spreadsheet, where I want to link each cell in a range to the google search page, where the search term is the text in this particular cell. So if I have a cell with content 'Bikes' I want to turn it to a link that will search for 'Bikes' in Google.

How do I achieve that short of manually going over each cell in the range and turning it into a link?

This is what I have:

before

Note that the list is much longer than a few cells.

This is where I want to arrive:

after

These will hyperlink to

  • google.com/search?q=Alpha
  • google.com/search?q=Beta
  • google.com/search?q=Gamma
  • google.com/search?q=Delta
  • etc...

Since there are a hundred of cells I would like a more practical method than inserting a hyperlink manually for each cell.

| improve this question | |
  • Add more details about the kind of solution that you want. – Rubén Jun 4 '15 at 20:23
  • @Rubén, please let me know what details in your opinion are missing and I'll gladly add them. No need to downvote only because you do not understand the question. Help me improve the question: Explain what you did not understand and ask for clarification, which I'll give to the best of my abilities. – Andrew Savinykh Jun 4 '15 at 20:47
  • Actually that is the way that it's suppose Stack Exchange sites work: If a user consider that a questions require improvement he could downvote. It's suggested that a explanatory comment be add too. Regarding my previous comment, I want to mean that you should mention if you are able to adopt a custom function / code or require only built-in functions. – Rubén Jun 4 '15 at 22:02
  • 1
    @Rubén thank you for clarification. Glad we sorted this out. I'm not against a custom function as long as the job is done, but it looks like we collectively figured out a way to do without. Cheers. – Andrew Savinykh Jun 4 '15 at 22:05
2

You could use INSERT > Link... to add a link to the active cell but it will require you to write the URL.

If you want that the URL be added automatically, you will need a Google Apps Script.

Instead, you could use a formula with the HYPERLINK function in a temporary column and use some built-in functions to do bulk operations.

I.E. if the search term is in A1 then use the following formula:

=hyperlink("www.google.com/search?q="&A1,A1)

Fill down as necessary.
Copy and paste as values over the column with the search terms.
Delete the temporary column.

Related keyboard shortcuts

Insert a link: CTRL+k
Copy: Ctrl+c
Paste as values: Ctrl+Shift+v

References

| improve this answer | |
  • That would create another cell. I do not need another cell I'd like to convert existing range of cells into hyperlinks – Andrew Savinykh Jun 4 '15 at 19:57
  • 2
    @zespri The new column need not be permanent. After creating it you can copy (Ctrl-C) and paste-values (Ctrl-Shift-V) back into column A, then delete the temporary column. Will take you far less time than writing a script, that's for sure. – user79865 Jun 4 '15 at 21:40
  • 1
    @HomegrownTomato, oh that's neaaat! Did not know the trick about pasting with Shift! That will solve it. – Andrew Savinykh Jun 4 '15 at 21:45
  • I have edited the answer to integrated @HomegrownTomato comment, that helped me. Rubén, please feel free to review and change if you can think of a better wording. Cheers. – Andrew Savinykh Jun 4 '15 at 21:52
1

You can use the HYPERLINK function. In your example you have the values in column A. Do this:

  • Paste the following into another column, say, in B1 cell: =hyperlink("www.google.com/search?q="&A1,A1)
  • Now copy this B1 cell (CTRL+C) and paste it to the required range in the same column as this new cell, in your example B2:B4. (Select the range and press (CTRL+V)
  • Now you have what you wanted to have in column A but in column B. Select column B, copy it (CTRL+C) and then paste values to column A (CTRL+SHIFT+V) overwriting the original content of the column.
  • Now you can clear column B and you are done
| improve this answer | |
  • Since my gentle edit to the original answer was rejected, I decided to document the solution that has worked in the end in a separate answer, outlining exactly the steps that have worked for me. Thank you Rubén and Homegrown Tomato for contributing – Andrew Savinykh Jun 4 '15 at 23:55

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.