Use Excel to Write Repetitive HTML Code

I have to maintain a rather old website that consists of nothing but flat HTML files. Until I get around to finally updating it to dynamic, database driven web site, I’m stuck with editing several dozen complex web pages. Every so often, I have to edit or create a new page that consists of long lists. While lists themselves aren’t complicated, it’s very time consuming to write all the HTML associated with a list of many items. Thankfully, I call upon a little trick that I’ve learned that uses Excel to write 99% of the code for me!

To get the most “bang for your buck” out of this trick, it helps to already have a list of items in excel. If you don’t, it isn’t a big deal. You’ll just have a little extra work ahead of you. But don’t worry, if you’re list has more than ten or so items, it’s probably still worth it to break out the Excel and put it to work.

Before we open up Excel, though, let’s do a little bit of planning. Open up your favorite web page editor (I’m using Visual Web Developer 2008 Express Edition) and create just your very first list item with HTML. We’re going to use this as our template in Excel. For example, my code looks like this:
<ul>
  <li><a href="http://www.examplesite.com">The B-52’s - Rock Lobster</a></li>
</ul>

As you can see in the code above, a list like this that has many items is going to have repetitive HTML tags and attribute code. For example, the <li> </li> tags will be in every list item. Also, the anchor tag and href, <a href=""></a> will be the same for each list item as well. Instead of writing this code over and over or doing multiple Copy and Pastes, we’ll let Excel do the heavy lifting and write all this code for us.

Open up your list in Excel. If your list isn’t in Excel, a quick Copy and Paste should do the trick. We’re going to break up the code we wrote into sections. Insert columns before and after your list items to make room for the HTML code. For my example, I typed the opening list item tag and the opening anchor tag with the href: <li><a href=" in the first column, first row (cell A1). Between the URL’s and song titles that were already in my list, I inserted another column, and typed the end quote and angled bracket for the opening anchor tag: "> In the column after my song title, I typed the ending anchor and list item tags: </a></li>

In the very next column, we need to create a quick formula that concatenates (connects) all of the columns to the left. In my example, columns A – E will be concatenated. Here’s the formula to input in the last column: =CONCATENATE(A1,B1,C1,D1,E1). You’ll notice that if you enter this formula correctly, all of the columns to the left will be connected together. This will make copying and pasting much easier and will also resulting in cleaner code (skipping the concatenation usually results in unneeded spaces within your lists).

Now for the fun part! Select cell A1 and double click the small square located in the bottom right corner. This will populate that entire column with the contents of cell A1 where ever there is a value inside an adjacent cell. Do this for the other cells that have HTML in them as well as the column that has our concatenation formula.

That’s it! Now all you have to do is copy and paste the column with the concatenate formula into your web editor, inside the opening and closing (un)ordered list tag. Repeat these steps for all of your long lists and you’ll shave a lot of time off of your work!