Dengar's Blog Logo

Dengar's blog...
We will see what I post, probably will be some random tutorials

Tuesday, 6 June 2017

LibreOffice - concatenate a string with seperator (Google Sheets join)

if you're in need to join a list of strings with a separator, you can create this custom function in your sheet:


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

set this up by selecting: tools > macros > organise macros > libreoffice basic ...
you can choose where to save the macro to the sheet, the application or others.