Ask Your Question
1

(Calc) Make rows of text into google search hyperlinks [closed]

asked 2013-06-30 09:52:06 +0200

Marty McFly gravatar image

updated 2015-11-03 05:26:22 +0200

Alex Kemp gravatar image

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)

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-03 05:26:53.651918

Comments

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.

Marty McFly gravatar imageMarty McFly ( 2013-06-30 14:53:27 +0200 )edit

4 Answers

Sort by » oldest newest most voted
1

answered 2013-07-04 20:22:44 +0200

m.a.riosv gravatar image

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

image description

edit flag offensive delete link more
0

answered 2013-07-04 02:24:09 +0200

JKEngineer gravatar image

updated 2013-07-04 06:17:22 +0200

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.

edit flag offensive delete link more
0

answered 2013-06-30 13:34:58 +0200

m.a.riosv gravatar image

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")

edit flag offensive delete link more
0

answered 2013-07-05 00:11:49 +0200

JKEngineer gravatar image

updated 2013-07-05 00:26:00 +0200

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("https://www.google.com/search?q="&A1&"&ie=utf-8&oe=utf-8&aq=t&rls=Palemoon:en-US:official&client=firefox-a&channel=fflb",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.

screenshot with three rows filled and cell B1 open

HTH

edit flag offensive delete link more

Comments

"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("https://www.google.com/search?q="&A1&"&ie=utf-8&oe=utf-8&aq=t&rls=Palemoon:en-US:official&client=firefox-a&channel=fflb";CURRENT())

hoakzie gravatar imagehoakzie ( 2014-01-03 00:54:12 +0200 )edit

Question Tools

Stats

Asked: 2013-06-30 09:52:06 +0200

Seen: 1,956 times

Last updated: Jul 05 '13