EVGA

LibreOffice: Concatenate array of cells (solution)

Author
Post
Essentials Only Full Version
pagelm
SSC Member
  • Total Posts : 831
  • Reward points : 0
  • Joined: 2008/06/30 11:12:08
  • Status: offline
  • Ribbons : 9
2014/04/24 10:41:55 (permalink)
In libreoffice there's no built-in way to concatenate the text of all cells in a range together (i.e. to create a condensed CSV-style list. I call this CSV-ifing).
 
This BASIC macro accomplishes it (simply type e.g. "=CSVIfyArray(A1:C10)" after creating the macro as follows in "My Macros & Dialogs/Standard/Module1)

 
Function CSVifyArray(pArray(),OPTIONAL sep as String,OPTIONAL ColFirst as Boolean) as String
IF IsMissing (sep) THEN sep=","
IF IsMissing (ColFirst) THEN ColFirst=FALSE 
Dim i as Long, j as Long, ib as Long, ie as Long, jb as Long, je as Long
Dim rText as String
Dim item as String
Dim addStr as String

if NOT(ColFirst) THEN
    ib = Lbound(pArray(),1) REM i holds row indexes
    ie = Ubound(pArray(),1)
    jb = Lbound(pArray(),2) REM j holds column indexes
    je = Ubound(pArray(),2)
ELSE REM PivotTable-like implementation
    jb = Lbound(pArray(),1) REM j will hold row indexes
    je = Ubound(pArray(),1)
    ib = Lbound(pArray(),2) REM i holds column indexes
    ie = Ubound(pArray(),2)
ENDIF    

rText = ""
    For i = ib to ie
        For j = jb to je
            IF NOT(ColFirst) THEN item = CStr(pArray(i,j)) ELSE item = CStr(pArray(j,i))
            IF (item = "0") THEN item = "" REM this was so blank cells do not contribute.  If "0" is a legitimate value for you, you may need additional (or less) logic here 
            IF (rText <> "") THEN addStr = sep + item ELSE addStr = item
            IF (item <> "") THEN rText = rText + addStr
        Next j
    Next i
CSVifyArray = rText
End Function REM CSVifyArray
 

Implementation expanded from http://en.libreofficeforum.org/node/6415 (specifically post #8)
post edited by pagelm - 2014/04/24 10:44:20
#1

2 Replies Related Threads
    sk2play
    CLASSIFIED Member
    • Total Posts : 4437
    • Reward points : 0
    • Joined: 2009/07/12 15:19:13
    • Location: Westminster, CO
    • Status: offline
    • Ribbons : 8
    Re: LibreOffice: Concatenate array of cells (solution) 2014/04/24 23:42:43 (permalink)
    thanks
    post edited by sk2play - 2014/04/24 23:45:57

    Corsair R500 Case, H110 Hydro, 1200AX PSU, Asus Maximus Hero VI MB, Intel 4770K CPU, Gigabyte GPU GV-N78TGHZ-3GD, G-Skill Trident 2400MHz 16GB, Crucial M500 960GB SSD, Seagate 2TB HDD x2, Creative SBz to Onkyo TX-DS676, Bose 201's, Klipsch Sub XW-300d, HP ZR30w 30" S-IPS LCD, W8.1
    #2
    pagelm
    SSC Member
    • Total Posts : 831
    • Reward points : 0
    • Joined: 2008/06/30 11:12:08
    • Status: offline
    • Ribbons : 9
    Re: LibreOffice: Concatenate array of cells (solution) 2014/08/15 14:26:16 (permalink)
    Excel equivalent

    Function CSVifyArray(inRng As Range, Optional sep As String = ",", Optional ColFirst As Boolean = False) As String

    Dim i As Long, j As Long, ib As Long, ie As Long, jb As Long, je As Long
    Dim rText As String
    Dim item As String
    Dim addStr As String
    Dim pArray As Variant
    pArray = inRng.Value

    If ColFirst = False Then
        ib = LBound(pArray, 1) 'i holds row indexes
        ie = UBound(pArray, 1)
        jb = LBound(pArray, 2) 'j holds column indexes
        je = UBound(pArray, 2)
    Else 'Rem PivotTable-like implementation
        jb = LBound(pArray, 1) 'REM j will hold row indexes
        je = UBound(pArray, 1)
        ib = LBound(pArray, 2)
        'REM i holds column indexes
        ie = UBound(pArray, 2)
    End If
    rText = ""
        For i = ib To ie
            For j = jb To je
                If Not (ColFirst) Then item = CStr(pArray(i, j)) Else item = CStr(pArray(j, i))
                If (item = "0") Then item = "" 'Rem this was so blank cells do not contribute.  If "0" is a legitimate value for you, you may need additional (or less) logic here
                If (rText <> "") Then addStr = sep & item Else addStr = item
                If (item <> "") Then rText = rText & addStr
            Next j
        Next i
    CSVifyArray = rText
    End Function 'CSVifyArray

    #3
    Jump to:
  • Back to Mobile