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: 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")