This is a continuation of previous post, in which I talked about the potential use of Smart Lookup for an Excel dashboard. However there is limitation. It drove me to think of an alternative.
Instead of asking user to trigger Smart Lookup, I created a dynamic hyperlink that sends user to Google Search result based on the content in a cell.
Here’s come this post.
In order to achieve this, I googled a bit to find out the URL I need for a google search result, which is indeed quite simple.
For example, if I want to google “wmfexcel“, the following URL would do:
https://google.com/search?q=wmfexel
If I want to google “beexcellence“, the following URL does the job:
Did you see that, the prefix for the search URL is constant:
https://google.com/search?q=
The variable part is the text string that being joined after the “=” sign. In our examples, wmfexcel and beexcellence are the variables.
Once we’ve find this pattern, it is easy in Excel to create the dynamic URL using HYPERLINK function:
The Syntax
HYPERLINK(link_location, [friendly_name])
Here we go:
=HYPERLINK("https://google.com/search?q=" & A1,"Google "& A1) where A1 resides the content you want to google about
The logic is straight forward. We concatenate the prefix URL with the search content that resides in a cell, followed by giving it a user-friendly name.
As simple as this. 🙂
Try it out. Hope you like it.
it worked for me thank you vert much
LikeLike
You are welcome! Glad it helped!
LikeLike
Thank you for this. I am getting a #VALUE! error when entering more than 13 characters in the reference cell. Any suggestions?
LikeLike
Hi,
I’m having trouble performing this as the text that I have to search includes quotation marks. This results in double quotations and a value error. Is there anyway to make it work?
Thanks!
LikeLike
Amazing, saves me so much time. Thank you for that. Can I ask how I can automatically put the link of the first search result in another cell?
LikeLike
You are welcome. Glad that it helped. And you have a very good question… which I don’t have an answer. 😅. Hope there will be answers from other experts.
LikeLike
Wow, this is amazing! Going to save me tons of time on my project! Thank you so much!
LikeLike
You are welcome! Glad it helped. 😀
LikeLike
what if we have spaces in the string ?
LikeLike
It will be the same. Nothing needs to be revised in the formula.
LikeLike