ISBN to HTML in Excel

November 22, 2016 11:56 AM

Last year I found instructions for turning a list of ISBNs into hyperlinks to our library computer using Excel. I am at a new library and have lost the file. I'll give more details below.

So the way this worked was the ISBNs were in one column. Another column had a function that would stick the ISBN into our catalog search string. Then other functions would wrap the html tags around that. Finally in the last column there was a fully formed link that I could copy and stick on a website. It was great!

Do you have any idea what I am talking about? This method did not use Excel's built in HTML tool.

posted by Biblio to Computers & Internet (6 answers total)

I think I could probably do it if I knew what your library's search strings looked like.

I also could possibly do you one better... like a browser plugin or something similar where you go to your browser and instead of entering a url, you enter something like

isbn:YOURISBNHERE

and it magically goes to the right place. There are a lot of options, really.
posted by RustyBrooks at 12:02 PM on November 22, 2016

In fact I just tried this myself in chrome and you might like it, it's quite easy to do. Other browsers probably have something similar. I use it a lot myself, for example I have little custom search terms for JIRA and other stuff I use a lot at work. It kind of just depends on what your work flow is like and what you're trying to do.
posted by RustyBrooks at 12:07 PM on November 22, 2016

Best answer: The formula probably looked like one of these:

=HYPERLINK("https://catalog.nypl.org/search~S1?/i" & A1, B1)

OR

="<a href='https://catalog.nypl.org/search~S1?/I" & A1 & "'>" & B1 & "</a>"

Where A1 is the ISBN and B1 is the title. If you don't have a title and want to use the ISBN as the link text, use the same reference (in this case, A1) twice. The first creates a clickable link you can copy and paste in most website rich text editors. The second creates the HTML.

I'm using NYPL as an example, but your catalog must have a similar way of searching by appending the ISBN at the end of the link.

Once you get the formula to work for your first row, copy it down the spreadsheet by clicking and dragging the square at the bottom right of the cell with the formula. Then, when you fill in additional ISBNs, the links will automatically appear.
posted by beyond_pink at 12:22 PM on November 22, 2016 [1 favorite]

Best answer: You know how you can use the & operator to stick text together in Excel, right? If I'm reading your post right, your spreadsheet had an ISBN in e.g. cell A1, and then a formula like "http://our-website.com/search?isbn="&A1 in B1, and finally something like "<a href='" & B1 &"'> " & A1 & " </a> in C1.
posted by Dr Dracator at 12:25 PM on November 22, 2016 [1 favorite]

Response by poster: Yes, that's it! I can take it from here! You rock!
posted by Biblio at 12:55 PM on November 22, 2016 [3 favorites]

Response by poster: I just wanted to add that I was able, after some trial and error, to add table tags so my resultant list comes out all nicely formatted. Thanks again!
posted by Biblio at 9:30 AM on November 25, 2016