A concatenate function on steroids for OpenOffice calc

In OpenOffice / LibreOffice Calc, you would normally use CONCATENATE to combine strings from different cells, like in the example below:

	A	B	C
1	Combine	This	Combine this

You can get C1 by combining A1 and B1 using one of the two options below:

=CONCATENATE(A1, " ", B1)

=A1&," ",&B1

But what if you have the following table:

	A	B	C	D	E	F
1	5	4		5

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:

=STRJOIN(A1:E1,",")

will give you 5,4,5

or:

=STRJOIN(A1:E1,",","(",")")

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:

=CONCATENATE("c(",STRJOIN(A1:E1,","),")")

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

Again, check out the original post on Stackoverflow for further instructions (scroll down for the improved version by Adam Spiers).

This work is licensed under a Creative Commons Attribution-ShareAlike 2.5 Generic License.

About these ads