JOIN function for Excel
I wanted a simple function that works like CONCATENATE, but is simpler to use on a range of cells. If you want to join the contents of cells in A1 to D1, you’d need:
=CONCATENATE(A1, B1, C1, D1)
That’s kind of a pain. With this custom function, you can type:
=JOIN(A1:D1)
Better, no? You can also use an optional separator, e.g. if you want a comma-separated list. Here’s the code, to paste into a VBA module in Excel:
Function Join(rng As Range, Optional sep As String) As String
'Kind of a more sophisticated concatenate
'because it works with a range of cells, and it can use an optional separator
'loosely based on Python's join method for arrays
Dim cell As Range
Dim str
For Each cell In rng
If Not IsEmpty(cell) Then
str = str & cell.Value & sep
End If
Next cell
'Strip off the trailing separator
str = Left(str, Len(str) - Len(sep))
Join = str
End Function