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).

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

About these ads

8 thoughts on “A concatenate function on steroids for OpenOffice calc

  1. Pingback: Combining text from data frame in one text string in R | Ecostudies

  2. Pingback: Concatenate in SQLite | Ecostudies

  3. John

    I concatenated two cells (with text) into another cell (c1=b1+d1) but when I highlight the resultant cell it shows the concatenate function instead of the combined text. If I then delete the other two cells, the contents goes away.
    Help?

    Reply
    1. pvanb Post author

      Hi John, Using a formula or function like concatenate will show you the result in the cell. However, when you select (highlight) the cell, the formula rather then the result is shown in the formula bar. This is because the cell contains the formula, not the result. This is also why, if you delete the cells b1 and d1, the formula combines an empty cell with an empty cell = empty cell.

      Reply
  4. dan

    Simply awesome, they dont mention this function enough, i’ve been using the concatenate function in ridiculous ineffcient ways, now with this i can get around it. Thank you!

    Reply
  5. Ian

    I had expected the CONCATENATE function to be able to accept a range as well as a list but when I found it didn’t, and it seemed madness to name each cell individally, I figured it must be a common enough need that I could get some clues for a workaround from the internet. Lo and behold, not just some clues but a whole solution in the form of STRJOIN. Cool.

    Reply
  6. Aaron

    Hi, Im trying to set this up, but it keeps giving me an error when I run the macro. Argument is not optional. Any ideas on what might casue that, sorry bit of a newb to Open office

    Reply
    1. pvanb Post author

      Hi, I am not sure. You might want to contact the maker of this macro, see the link to the original post on Stackoverflow in the blogpost

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s