Christoph Polcin

My Libreoffice Concatenate Cells Macro

Li­bre­Of­fice is a modern heavy­weight, formerly OpenOffice. The built-in CONCATENATE function accepts only single cells as arguments but not a range of cells. This has always bugged me. To con­cate­nate a range of cells you must create a macro, which does the work for you. Afterwards you can use the function across your document. Andrew Pitonyak has written a nice and profound macro doc­u­men­ta­tion for OpenOffice, which is available as book too. These docs will also work for Li­bre­Of­fice. Likewise, the official doc­u­men­ta­tion is a good starting point.

To create custom macros you need to open a document and execute Tool > Macros > Organize Macros > LibreOffice Basic from the ap­pli­ca­tion menu. Select your document, create a new module and paste the snippet. Save the document and close the window. Now you can use =MyConcat($A1:$A99, "; ") or =MyConcat($A1:$F1) to con­cate­nate a range of cells.

REM  *****  BASIC  *****

Sub Main

End Sub

Function MyConcat(arr(), Optional sep as String) as String
Dim i as String
Dim r as String
Dim s as String

If Not IsMissing (sep) Then   
    s = sep
    s = ", "

r = ""
For Each i In arr()
    If i <> "" Then
        r = r & i & s
Next i

If Len(r) > Len(s) Then
    r = Left(r, Len(r)-Len(s))

MyConcat = r

End Function