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

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?

---

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 enter a new Macro name and 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

<foo@bar.com>, <baz@qux.org>, <another@email.address>, <and@so.on>

---

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

---

Ever so often I'd enjoy the ease and quickness of replace & calculation Options as well as in general the quick handling & modifying Options, when once again sitting in front of a dumped-file-lists or whatsoever.

I never understood why they didn't include such an essential function right from the start, really.

It's based on Adam's script, but with the extension to swap CONCAT from horizontal to vertical, while still keeping the delimiters in order.

Function CONCAT2D(Optional range,   Optional delx As String, Optional dely As String, _
                                    Optional xcell As String, Optional cellx As String, _
                                    Optional swop As Integer)
    Dim xy(1), xyi(1), s(1) As Integer
    Dim out, cell, del, dxy(1) As String

    'ReDim range(2, 1)           'Gen.RandomMatrix 4 Debugging
    'For i = LBound(range, 1) To UBound(range, 1)
    '   For j = LBound(range, 2) To UBound(range, 2)
    '       Randomize
    '       range(i,j) = Int((100 * Rnd) )
    '   Next
    'Next

    out  = ""
    If IsMissing(delx)  Then : delx  = ","      : End If
    If IsMissing(dely)  Then : dely  = delx()   : End If
    If IsMissing(xcell) Then : xcell = ""       : End If
    If IsMissing(cellx) Then : cellx = xcell()  : End If
    If IsMissing(swop)  Then : swop  = 0        : End If
    dxy(0) = delx() : dxy(1) = dely()
    xyi(0) = 1      : xyi(1) = 2
    If swop = 0     Then :  s(0) = 0 : s(1) = 1
                    Else    s(0) = 1 : s(1) = 0 : End If

    If NOT IsMissing(range) Then
        If   NOT IsArray(range) _
        Then :  out = xcell & range & cellx
        Else    del = delx
                For xy(s(0)) = LBound(range, xyi(s(0))) To UBound(range, xyi(s(0))
                For xy(s(1)) = LBound(range, xyi(s(1))) To UBound(range, xyi(s(1))
                    cell = range(xy(0), xy(1))
                    If cell <> 0 AND Len(Trim(cell)) <> 0 _
                    Then :  If out <> "" Then : out = out & del : End If
                            out = out & xcell & cell & cellx
                            del = dxy(s(0))
                    End If
                Next :      del = dxy(s(1))
                Next
        End If
    Else        out = "ERR"
    End If

    CONCAT2D = out
End Function