Using spreadsheet formulas to mark-up HTML - Part 1

 by Martin Belam, 11 May 2010

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.

Spreadsheet example image

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:

Spreadsheet example 2

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.

5 Comments

And tomorrow's lesson for Professional HTML Programmers: How to generate random words out the air by using the dictionary "flip and finger press" method and pass it off as copy!

Great idea.. until you have a spreadsheet which interprets (or adds) a 'smart quote', or which interprets the URL as a URL and embeds the hyperlink (meaning your formula column is going to turn into something really weird - a hyperlinked hyperlinked? Into the 4th dimension?).

The smart quote thing is particularly dangerous, as it's often hidden in the prefs: I spent an incredibly frustrating hour trying to work out why a spreadsheet wasn't working as a table (yup, on the Gdn): it turned out to be because of precisely that.

While this is nice if you have a stack of plain links like that, I can't think of the occasion where it might arise. Far more useful, surely, for journalists writing copy who need to insert links for web content is to be familiar with blogpost writing programs such as (on the Mac) ecto or Marsedit, which make it simple.

A lot can be done with spreadsheets - but a lot can be done wrong with them too.

Three cheers! I am an Excel jock turned Wordpress / Drupal hacker, and I am damned mad that I did not think of this myself. Nice work. Am now following your tweets.

Martin,

If you use the mailmerge function in MS Word, you can produce even more creative outputs based on MS Word. You can use it to compile whole directories if you have the data, though you have to be a bit careful with the way Word interprets things.

Very informative article, bloody good work around after reading it, oh the power of hindsight.

Keep up to date on my new blog