Open Office Spreadsheet (Calc) - Concatenate text cells with delimiters

Question

I am using Open Office's spreadsheet program and am trying to concatenate several text cells together with delimeters. For example, suppose I have the cells below:

+--------+
| cell 1 |
+--------+
| cell 2 |
+--------+
| cell 3 |
+--------+
| cell 4 |
+--------+
| cell 5 |
+--------+

I would like to concatenate them with delimiters so that the result is in one cell like this one:

+----------------------------------------------+
| (cell 1),(cell 2),(cell 3),(cell 4),(cell 5) |
+----------------------------------------------+

My first thought was to try and make a macro or something, but I don't think open office supports those. Any ideas?

Answer 1

Well, after a lot more searching and experimenting, I found you can make your own functions in calc. This is a function I made that does what I want:

Function STRCONCAT(range)
    Dim Row, Col As Integer
    Dim Result As String
    Dim Temp As String

    Result = ""
    Temp = ""

    If NOT IsMissing(range) Then
     If NOT IsArray(range) Then
      Result = "(" & range & ")"
     Else
      For Row = LBound(range, 1) To UBound(range, 1)
       For Col = LBound(range, 2) To UBound(range, 2)
           Temp = range(Row, Col)
        Temp = Trim(Temp)
        If range(Row, Col) <> 0 AND Len(Temp) <> 0 Then
            If(NOT (Row = 1 AND Col = 1)) Then Result = Result & ", "
         Result = Result & "(" & range(Row, Col) & ") "
        End If
       Next
      Next
     End If
    End If

    STRCONCAT = Result
End Function

Answer 2

IMO such function has very close to "must have" in OO.

Answer 3

cell1&"' "&cell2"' "&cell3"' "&cell4

or am I missing something here

Answer 4

Thank you Markus O'Reilly!

Does anyone know how to modify that function so that it lists empty cells as let's say "NULL" string? I tried but my experience with ?Visual Basic? equals 0.

The range comes in as an array with zeroes instead of empty cells. Is there another way of passing range to a function?

Answer 5

Thanks a lot Markus for finding a solution to this.

Here are some slightly more detailed instructions for the benefit of OpenOffice Basic newbies like myself. This applies to version 3.1:

Tools -> Macros -> Organize Macros -> OpenOffice.org Basic...

Now select from the explorer tree where you want your function live, e.g. it can be in your own macro library (My Macros / Standard) or stored directly in the current spreadsheet.

Now click New to open the OO.org Basic IDE. You'll see a REM statement and some stub Sub definitions. Delete all that and replace it with:

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

The above code has some slight improvements from Markus' original:

• Doesn't start with a delimiter when the first cell in the range is empty.

• Allows optional choice of the delimiter (defaults to ","), and the strings which go before and after each non-blank entry in the range (default to "").

• I renamed it STRJOIN since "join" is the typical name of this function in several popular languages, such as Perl, Python, and Ruby.

• Variables all lowercase

Now save the macro, go to the cell where you want the join to appear, and type:

  =STRJOIN(C3:C50)

replacing C3:C50 with the range of strings you want to join.

To customise the delimiter, instead use something like:

  =STRJOIN(C3:C50; " / ")

If you wanted to join a bunch of email addresses, you could use:

  =STRJOIN(C3:C50; ", "; "<"; ">")

and the result would be something like

<[email protected]>, <[email protected]>, <[email protected]>, <[email protected]>