Using spreadsheet formulas to mark-up HTML - Part 2

In yesterday's post I showed how thinking a bit like a programmer could help save you time entering a list of links into a cumbersome WYSIWYG editor. The key is to realise that computers are very good at automating anything where you find yourself acting on patterns. If you can spot the patterns, you should be able to engineer a shortcut.

To examine this idea further, let us imagine that the list of links you want to insert into your article is a list of Twitter accounts. Based on yesterday's post, you'd end up with a spreadsheet where the opening two columns were a list of Twitter URLs and a list of Twitter usernames:

Can you spot the pattern?

If all of the usernames start with '@', and all of the URLs start 'http://twitter.com/', but the username itself remains the same, then you don't need to type those things in every time. There is a recurring pattern that revolves around the username, so build that into your formula.

In the A column of your spreadsheet you just need a list of the usernames, minus the @ symbol. Then in the B column you need a formula that inserts the full web address and @ symbol into every line. It will look something like this:

="<a href=""http://twitter.com/"&A1&""">@"&A1&"</a><br />"

Again, every time you want a " character to come out in your final HTML, you'll need to double it up in the spreadsheet formula. Your particular CMS may require that 'lists of things' are more properly defined by putting list item <li> tags around them, rather than using <br /> to split up the lines. If so, simply adjust your formula accordingly.

="<li><a href=""http://twitter.com/"&A1&""">@"&A1&"</a></li>"

I've made an example spreadsheet in Google Docs for you to take a look at.

Of course, there are plenty of other ways that you could achieve this result. If you can program a bit of Ruby or PHP or Perl, you could put all of the Twitter usernames into a text file, and write a short script to iterate over them creating the HTML. This spreadsheet methodology is just to illustrate how if you are doing repetitive tasks in your CMS, like clicking a link icon and entering a web address over and over, you can usually find a way to automate the task.

Tomorrow, I'll take this example a little bit further still, and show how you can get your spreadsheet to make some decisions for you based on the information you give it.