Let Excel Do Your Searching For You

I thought I would pass on a neat trick I figured out that can makes Excel automatically run searches in a search engine / online catalogue of your choice.

Here’s an example of what your list might look like:

search

You can set up a hyperlink function so that column “B” searches what is in the correpsonding cell in column “A”.

So simply clicking on cell B2 opens up the following webpage in your default browser:

Well played, Old Sport.

Well played, Old Sport.

Step 1: Find the search link

Let’s say I want my spreadsheet to search WorldCat. I go there and search for bunnies.

Look at the url of the results page:

https://www.worldcat.org/search?qt=worldcat_org_all&q=bunnies

Copy that link.

Step 2: Make a hyperlink formula

The excel formula for a hyperlink is:

=HYPERLINK(“url”, “display text”)

So I type that into cell B2 (going by the template below), and copy the worldcat URL, and put “Search Worldcat” as my display text.

hyperlink

Step 3: Hack into the Mainframe!

Now, instead of searching for “bunnies” I want to search for whatever text is in cell A2.

I go into my formula bar and replace the word “bunnies” with the following:

“&A2&”

(I got this technique from this page.)

I work in a university library, and I developed this kind of spreadsheet for the purposes of checking how many copies of course texts we had in our catalogue. But it could be used for any of your mass-search needs, whatever they may be.

Advertisements

One Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s