Using spreadsheet formulas to mark-up HTML - Part 1

I blogged yesterday about the growing debate about whether journalists need to be able to write computer programs. I'd argue that knowing HTML mark-up is a must, but that it is thinking like a programmer that is more important to journalists than actually cutting code. In fact, I don't think this applies to just journalists, but to anyone publishing on the web. Today I'm going to show a simple example of using a spreadsheet like Excel to take some easy short-cuts.

Let us imagine that you need to include a list of links in your article. If you are using a WYSIWYG editor in your CMS, then the chances are that you have to type all the names in, then go back and add the hyperlinks in one-by-one by pressing a little button with an icon of a chain link, and filling in some text in the box that appears.

If, however, you can insert a chunk of HTML directly into the piece, you can save yourself some time with a spreadsheet.

Put the URLs you want to link to in column A of a spreadsheet, and put the title of the links in the corresponding row in column B, like so.

Now, in column C, you want to enter a formula that will wrap the right HTML around the information that you entered. In Excel it will look something like this:

="<a href="""&A1&""">"&B1&"</a><br />"

What is going on here?

Well, essentially you are asking Excel to add together some HTML+the URL+some HTML+the link name+some HTML into one long string.

The first bit - "<a href=""" opens up the A link tag. The next bit - &A1& - inserts the contents of cell A1, the first URL you entered. The third chunk - """>" finishes off the opening A tag. Then, &B1& inserts the link text from the spreadsheet. Finally "</a><br />" closes the A tag and adds a new line.

Why all of the quotes?

Well, Excel and most spreadsheets use the " symbol to mark the beginning and end of a string, and so if you want to actually include a quotation mark within the string, you need to put it twice. If the quotation happens to come at the beginning or end of the chunk of text you are trying to insert, as it does twice in this example, then you need to put three sets of quotation marks in a row.

Once you've entered that formula into C1, you can then copy it into all the cells in column C for as many items as there are in the list - thus:

Now, if you select all of the cells in column C, click copy, and then paste into a notepad text file or your CMS input box, you should find that you have got some neatly formatted HTML.

This idea should work in any spreadsheet program - and I've made an example document here using Google Docs.

Once you've got a spreadsheet like this saved somewhere, then any time you need a list of hyperlinks, you just change the values in columns A & B. If you need to wrap the links in <LI> tags to form a stuctured list, just add those into the formula.

Computers are great at performing repetitive tasks that manipulate text or numbers, and if you know how to get started, you can build lots of these little tools to save you time. Tomorrow I'm going to take this example a little bit further, to show how recognising a pattern can save you even more time.