Improving Excel and Yahoo Pipes

Microsoft Excel is brilliant. Except when it isn't. And a major place where it isn't is when you have an Excel spreadsheet with a number of cells containing strings (any sequence of ASCII characters) and you wish to concatenate all of these separate strings into one big string in another cell.

Excel does provide a function that, to the optimistic neophyte, would seem to do the job: It is, not surprisingly, called Concatenate.

The problem with Concatenate is that you can't tell the function to do its job for a range of cells (such as A1:A10). Nope, you have to list each cell to be munged together. It also doesn't support any kind of separator character to be placed between the concatenated strings. In short, Concatenate is about as useful as go-faster stripes on a modem.

I just needed to concatenate a whole mess o' strings in a spreadsheet (several hundred in groups into a score of big strings) and so I went a-lookin' for a solution and, stap me vitals, if I didn't stumble across the answer: A nice little VBA function created by Pearson Software Consulting. While many of us are probably quite capable of creating a similar function, why reinvent the wheel?

This neat little chunk of code allows you to sensibly concatenate literal text:

=StringConcat("|",TRUE,"A","B","C")

This returns "A|B|C". You can also concatenate text in a range of cells and even use a formula to filter the array:

=StringConcat(", ",TRUE,IF(B30:B39>4,C30:C39,""))

This will create a string containing a comma-separated list of all values greater then 4 in the given cell range.

Check out Pearson's Excel page for a lot more useful Excel functions including an Internet file download function and even a function to create Globally Unique Identifiers (GUIDs). Pearson gets a rating of 5 for their excellent free code.

While it is true that you can do many remarkable things in terms of transforming data using Excel, there are all sorts of transformations, particularly those that involve "live" data from the Internet, that Excel can't do easily.

To this end, you should check out Yahoo Pipes. Pipes, as its name suggests, provides a service that routes data from one or more sources to an output. In its simplest forms you could use a pipe to grab one or more input RSS feeds and merge them into a single output RSS feed. Or you might get fancy and filter the input feeds so only feed items containing, say, the term "oyster" appear in the output feed. (You would, of course, have to name this feed "The World is my Oyster Feed".)

Sign up for ITworld's Daily newsletter
Follow ITworld on Twitter @IT_world

• Email
• Print
• Share
  • Slashdot
  • Digg
  • Stumble
  • Reddit
  • Newsvine