Excel has many unlikely uses (did you know that Excel 97 contained a hidden Flight Simulator?). I’ve just reminded myself of another one: fast text templating.
I needed to convert a data structure that looked like this
into one that looked like this
In other words, I needed to convert from a delimited text string into XML elements.
It took me about 60 seconds in Excel. Here’s how:
- I copied the text containing all the keywords and pasted it into a single cell of an Excel spreadsheet
- I selected the cell, and clicked on the Text to Columns tool (you find this in the Data tab)
- In the Text to Columns Wizard, I indicated that the data was Delimited, and then entered the appropriate delimiter – the pipe (‘|’) character in this case. When I clicked Finish, Excel split each keyword into its own cell, going across the sheet.
- Next, I needed the keywords arranged vertically instead of horizontally – in rows instead of columns. So I selected all the columns by clicking the first cell, then pressing Ctrl+Shift+[Right Arrow] to select all the way to the last keyword. Ctrl-C copied all the keywords. After clicking in an empty cell, I selected the Paste Special option. In the Paste Special dialog, right at the bottom, you’ll find the Transpose option, which will convert columns of data into rows, and vice versa.
- In the cell next to the first keyword, I typed a formula that would wrap the keyword in the XML boilerplate. To concatenate strings use ‘&’, and to include quotes in a string use a double quote. So the formula I needed was
="<Keyword Name=""" & A3 & """ />"
- I selected that first cell again, and double-clicked the solid square at the bottom right of the cell’s selection border. This replicated the formula all the way down the page to the last row with something in it.
- Did the Ctrl-C/Ctrl-V dance to copy and paste the generated XML into my code file.
- Job’s a good ‘un!
