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

concatenation, openoffice.org, spreadsheet

Solution

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

Problem

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?

Original source