Write Repetitive HTML with Excel

Have you ever been in a situation where someone emailed you a Word document to go online, and you’ve got to wrap each item in tags? You could sit there and copy and paste each line into notepad, to get rid of that MSO tag crap and then go through and copy and paste each tag before and after each item. Or, if there are similarities that you can exploit, then you can do a search and replace. But what about when they are not? With a little quick setup in Excel, you can use the relative function to apply the tags and then copy and paste the output to your HTML document, saving you lots of time and energy.

Here is a very simple example. Let’s say you get a list of locations which need to be placed in a webpage and each one needs to be hyperlinked to another page (test.aspx) using the location name as the pageid querystring variable. First, let’s get all the locations into a column:

Then we set up the initial function in Excel’s function bar:

="<a href=""test.aspx?pageid="&A1&""">"&A1&"</a>"

The text in quotes are inserted text, and the ‘&’ operators are used to grab variables from cell positions. This is how it looks in Excel:

Once that is entered, we can then drag the function and apply it relatively to the other cells. What that means is that if the function contains data from cell A1, when applied to the second row it will grab the data from cell B1, and the third row will grab cell C1, etc. To do this, position your cursor in the lower right hand corner of the function cell to where it becomes a small black cross. Then drag it down the column to encompass your target cells. You will see the cells be populated with the relative functions/html code that you generated:

Voila! Copy and paste this column into notepad, and you’ve got 10 lines of code automatically written by Excel. While this example is relatively simple, it is a life saver in situations where you have to convert hundreds of items. And of course you can add more tags to the function, such as line items, p tags, etc.. Hopefully this will save you some time at work, so you can get back to surfing youtube.