September 18, 2009, 8:43 PM — 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".)
The beautiful thing about Pipes is it is driven by a Web-based graphical user interface that uses drag and drop to arrange functional blocks and draggable connections to wire them together.
For inputs you can use not only RSS, Atom, RDF and iCal feeds, but also CSV, XML, JSON and KML files, the text from whole Web pages or even pull in Flickr photos, Yahoo Local items, Yahoo Search and Google Base data through search queries.
But wait -- there's more! You can also use the Yahoo Query Language (YQL) Web Service (http://tinyurl.com/ncsv4d). YQL provides access to Internet data through SQL-like commands and returns it in XML or JSON format.
You can also add user input (date, time, numeric, text and URL formats) and define private variables, which can be used to store things such as private API keys so if you make the pipe details public your personal data isn't copied.
Next week we'll start building something cool using Pipes.
