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

Dinosaurs!

Today I had a bit of a laugh at Phil Bradley’s post on what happens when you ask Google about dinosaurs.

chris-pratt-velociraptor-jurassic-world

Stop confusing Google, you guys!

The first result when you search: “what happened to the dinosaurs” is a page from AnswersInGenesis.org (a Creationist organisation who argue against the theory of evolution), and is given Google’s V.I.P. treatment – a special box with large text that basically “here’s your answer – right here!”

Dinosaurs are used more than almost anything else to indoctrinate children and adults in the idea of millions of years of earth history. However, the Bible gives us a framework for explaining dinosaurs in terms of thousands of years of history, including the mystery of when they lived and what happened to them.

screenshot by Phil Bradley

Well, it IS an interesting answer, but not one I’d recommend to a kid for their school assignment.

I was quite amused, and got inspired to geek out and really dissect what went wrong here. So much so that it triggered me to start a blog about library and information stuff, which I’ve wanted to do for a while, but lacked the motivation until now. (Yay, first post! Hello world! etc)

I had a go at Googling the same thing. By the time I did, the V.I.P. box has been updated with a page from University of Illinois.

However, the first result under the page is still the Answers in Genesis page, and a book by Dr. Kem Ham (CEO of the same group) appears to the left of the web results:

dinosaurs

2 strikes outta 3.

Thanks for nothing, Google?

No, I wouldn’t say that. As far as I’m concerned, Google is doing its job, which is to return the most relevant and highest ranked web results to a particular query. The page titles use very similar to the words to the search query and it even sources a book which has nearly the same title. And as far as Google is concerned, AnswersinGenesis.org is a good website. It’s updated frequently, it doesn’t have viruses, and lots of other pages link to it.

It’s our job to find the information that answers our question.

But when Google is not forthcoming, how do we do it? It’s simple: search better.

Basically I think the problem is the search query. Traditionally speaking, searches are meant to be based on keywords. Typing in an actual question, like “what happened to…” is literally asking Google to spoon-feed you an answer. Generally, and if the question is simple enough, Google is smart enough to do it. e.g.:

Thx.

Thx.

Sometimes this function backfires, and the dinosaurs search is a fantastic example. It kind of reminds us that Google is not a knowledge machine, it’s just an internet search engine.

If we change our approach, we get better results immediately.

I took a keyword approach and searched for: dinosaurs extinction

That’s a bit more interesting.

Clearly “extinction” makes a better keyword than “happened to”.

Now if I really use my google-fu, and add a site:edu limiter to limit to websites with an educational domain, it gets better again (and cuts out those pesky Wikipedia results). Have a look:

dinosaur extinct edu

I HAVE SO MUCH KNOWLEDGE RIGHT NOW.

SO much better. You see, we were so preoccupied with whether or not we could type questions into Google, that we didn’t stop to think if we should.