In OpenOffice / LibreOffice Calc, you would normally use CONCATENATE to combine strings from different cells, like in the example below:
You can get C1 by combining A1 and B1 using one of the two options below:
=CONCATENATE(A1, " ", B1)
But what if you have the following table:
and you want to combine A1:E1, separating each number by a comma, but ignoring empty cells? Unless I am missing something, the two options above won’t work. For example, using the concatenate function:
=concatenate( A1, " ", B1," ",C1," ", D1, " ", E1, " ")
will give you 5,4,,5.
A quick search on the Internet gave me this custom made function STRJOIN, which works perfect. It combines all the text from cells, but unlike the standard concatenate function, it ignores empty cells. Moreover, it lets you define the delimiter as well as strings that go before and after each non-blank entry in the range. To use the same example as above:
will give you 5,4,5
will give you (5),(4),(5)
You can only combine text from a array of cells (A1:A10), not from individual identified cells or text like in concatenate (e.g., concatenate(A1,B10,C20, “A”)). But of course nothing stops you from combining the two functions STRJOIN and CONCATENATE, for example:
which will give you: c(5,4,5)
I am copying the macro function below for your (and mine) convenience. Full credit goes to Adam Spiers who wrote this function based on a function by Markus O’Reilly. Check out the post on Stackoverflow for more details.
Function STRJOIN(range, Optional delimiter As String, Optional before As String, Optional after As String) Dim row, col As Integer Dim result, cell As String result = "" If IsMissing(delimiter) Then delimiter = "," End If If IsMissing(before) Then before = "" End If If IsMissing(after) Then after = "" End If If NOT IsMissing(range) Then If NOT IsArray(range) Then result = before & range & after Else For row = LBound(range, 1) To UBound(range, 1) For col = LBound(range, 2) To UBound(range, 2) cell = range(row, col) If cell <> 0 AND Len(Trim(cell)) <> 0 Then If result <> "" Then result = result & delimiter End If result = result & before & range(row, col) & after End If Next Next End If End If STRJOIN = result End Function
This work is licensed under a Creative Commons Attribution-ShareAlike 2.5 Generic License.