If you’ve ever needed to join together the contents of cells in Excel, you may be aware that you’ve got a couple of choices.

Firstly, you can just use the ampersand symbol to concatenate values and cells, as follows:

=(A1&B1&C1&”Bob”)

But this gets tedious – every cell or value you want to add gets its own ampersand, meaning a lot of typing, a lot of ampersands and a long formula!

The CONCATENATE function

There is an alternative. The Concatenate function takes the cells or values to be concatenated as its parameters, meaning you no longer need all those pesky ampersands:

=CONCATENATE(A1,B1,C1,”Bob”)

That makes the cell contents significantly shorter, and much easier to read.

But there are still limitations. Firstly, a limitation on the number of cells or values that can be concatenated – that limit is 30 in Excel 2003 and earlier, and 255 in Excel 2997 or later.

But the concatenate function also leaves out one bit of functionality which – for my money – would be a real improvement. There’s no way of saying “Concatenate these cells, and put this delimeter in between them.”

So, if I want to get commas between A1, B1 and C1’s contents, I have to write:

=CONCATENATE(A1,”,”,B1,”,”,C1)

Hardly elegant.

So, I decided to write a little user-defined function (originally in response to a request from someonw I was training) which would resolve this. If you think it might be useful to you, you’re welcome to grab a copy!

The logic is pretty simple. There are two parameters – a range of cells to be concatenated, and optionally, a delimiter to use between the values in the range.

The code loops through the cells in the range, and appends each to the return value of the function. It also appends the delimiter in each case, which will be a zero-length string if one wasn’t supplied, before truncating the final output if needed to remove the trailing delimiter.

Here’s the code:

Function JoinCells(rng As Range, Optional ConcatenationChar As String = "") As String

Dim cell As Range, strOutput as String

For Each cell In rng
    strOutput = strOutput & cell.Value & ConcatenationChar
Next cell

If Len(ConcatenationChar) > 0 Then
    strOutput = left(strOutput,len(strOutput)-1) 
End If

JoinCells = strOutput

End Function