(Calc) Make rows of text into google search hyperlinks

[MartyMcFly]

Hello. I have a spreadsheet with just one column and thousands of rows of plain simple text. I would like to find a way to turn all these texts into hyperlinks that would redirect me onto google search.

I used Opera browser in past, nowadays I use Firefox mod named Palemoon (basically the same thing). In both browsers, there is a function, that when you enter a word (or words) into address bar, it will redirect you to google search of those words you entered.

I would like to find a way how to make hyperlink of say, line A2 … Marty is user and make hyperlink of it that would redirect you to google search.
When I search “Marty is user” in Palemoon, it gives me

https://www.google.cz/search?q=marty+is+user&ie=utf-8&oe=utf-8&aq=t&rls=Palemoon:en-US:official&client=firefox-a&channel=fflb

and I suppose that I can live with it giving me just:

https://www.google.cz/search?q=marty+is+user

if necessary. So, is there any way to achieve this ? I’ve tried to google for this functionality, but couldn’t find anything.

(Using 4.0.4 version)

[MartyMcFly]

Thank you for swift answer mariosv, but I’m not sure if I understood your suggestion correctly. I have one column of various items (it is a list of items) and they are a text. I click on them, nothing happens.

I would like to click on an item and be redirected to google search results of said item. And because the list is long, I am looking for automatic way to add this text/formula that will make hyperlink to google search from it. Key word is “automatic”, because of so many items.

[mariosv]

Select the column with links and Ctrl+H (Menu/Edit/Find&Replace), and do it as it is in the image.
Search for: .*
Replace with: =HYPERLINK("&")
Enable: Current selection
Enable: Regular expressions

[mariosv]

You can add a second column with the function hyperlink:

In cell A2:
https://www.google.cz/search?q=marty+is+user&ie=utf-8&oe=utf-8&aq=t&rls=Palemoon

In cell B2:
=HYPERLINK(A1;“text to show”)

[JKEngineer]

If the cell A1 has Marty is user, then cell B1 would have =“https://www.google.cz/search?q=”&A1
This uses text addition and the operator &, which acts like a + for text. It adds the text content of A1 to the text in the “”.
Then C1 would have
=HYPERLINK(B1 ,B1),
which makes the contents of B1 into a hyperlink and displays the full link. Clicking on C1 performs the search.
You may be able to add the additional text beyond the search words, they are not useful on my machine’s configuration.
The keys here are the & operator and the Hyperlink function that mariosv cited. You would create the formulas in cols B and C and copy them down to handle your entire table. You might be able to nest the text addition in the Hyperlink function and do it in one column. I did not try that.

[JKEngineer]

I looked at doing it in one step.

While the behavior of the spreadsheet was not completely consistent, in that sometimes just copying and pasting the text for the URL turned it into a hyperlink, the single step does work.

If cell A1 has Marty is user, then cell B1 can have:

---

=HYPERLINK(“" - Google Suche”,A1)

---

where I have used the .com not .cz google address. I included the additional text past the search term, and it reproduces as part of the url, but I don’t know what it does. As I show it here, the display of cell B1 will be the same as that of cell A1,

---

Marty is user.

---

It will still be a full hyperlink. If you want to see the hyperlink’s full text in cell B1, change the final A1 to B1.

I tested this on a Win XP SP3 machine running LO Portable 4.0.0.3
Note that the forum software turns the text that is configured like a hyperlink into a hyperlink. I entered plain text here and in the spreadsheet.

You can also enhance the text displayed, for example you could replace the cell reference with:

---

"search link for "&A1

---

to get a display like: search link for Marty is user.

Here’s a screen shot of a sample with three rows manually entered as desired search terms in column A and the contents of cell B1 (displayed) copied down through B3.

HTH

[hoakzie]

“If you want to see the hyperlink’s full text in cell B1, change the final A1 to B1.”

That will cause an error, you can do the following to make the text into the full hyperlink instead (i.e. use “CURRENT()”):

=HYPERLINK(“" - Google Suche”;CURRENT())