Using spreadsheet formulas to mark-up HTML - Part 3

Over the last few days I've been trying to show that thinking like a programmer can save you valuable time if you are in the online content publishing business. You don't need to know a programming language - the ability to spot patterns, repetitive actions, and a decent command of assembling formulas in a spreadsheet can provide you with some shortcuts.

I looked at using Excel to wrap some HTML around a list of links, and how recognising patterns can make that simpler. Today, in the last part, I want explore adding conditional elements into your spreadsheet.

Conditional statements are a programmer's way of telling a computer to make a decision. The one we are going to look at is the IF statement. The instruction tells the computer that IF something is true, do this thing, otherwise, do another thing.

In Excel spreadsheets these look something like this:

=IF(A1=1,"One","Not one")

That means, if the value of cell A1 is 1, then make this cell say "One", otherwise say "Not one".

That isn't a particularly useful example, so let us return to our list of links idea. Let us say that you need to mark some of the links as belonging to an 'external site':

What we can do is add an extra column into our spreadsheet, and mark the external links as just that - 'external'. Let us modify our original formula from part 1 to leave off the <br /> that gave us a newline in our list, so that it now looks like:

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

In column E, we put our new IF statement:

=IF(C1="external"," [External link]<br />","<br />")

Finally we need to stitch our two snippets of HTML together by adding column F:

=D1&E1

Now, if you select all the cells in column F and paste them into a text document or a CMS form that accepts HTML, you should get your list with all the links in place, plus the external links correctly indicated.

Again I've put up a sample spreadsheet on Google Docs for you to look at.

Of course there are other approaches you could use, but I hope I've shown that with a bit of programmer-style thinking, even if your coding skills only run to entering formulas into Excel, you can build yourself some tools to save yourself some time manipulating text in your CMS.