My Libreoffice Concatenate Cells Macro
LibreOffice is a modern heavyweight, 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 concatenate 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 documentation for OpenOffice, which is
available as book too. These docs will also work for LibreOffice.
Likewise, the official documentation 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 application
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 concatenate 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 Else s = ", " EndIf r = "" For Each i In arr() If i <> "" Then r = r & i & s EndIf Next i If Len(r) > Len(s) Then r = Left(r, Len(r)-Len(s)) EndIf MyConcat = r End Function