How to concatenate 50 columns in libreoffice using macros?

[varun]

Below the vba code which i want to run in libreoffice

Sub parsing()
For j = 1 To 1742
  For i = 1 To 1720
    If InStr(1, Cells(1, j), "Performance") <> 0 _
       And InStr(1, Cells(1, j), "Tire") <> 0 Then
        If IsEmpty(Cells(i, j)) = False Then
          Cells(i, 1744) = Cells(i, 1744) & ", " & Cells(i, j)
      End If
   End If
  Next i
Next j
End Sub

[JohnSUN]

This will not work in LibreOffice Calc. Your code is designed for a table of 1744 columns. Unfortunately, in LibreOffice you can use only 1024 columns.

[JohnSUN]

If your data does contain a valid number of columns, you can use this function:

Function myConcat(aStrings As Variant) As String
Dim i As Long, j As Long, sRes As String 
	sRes = ""
	For i = LBound(aStrings,1) To UBound(aStrings,1)
		For j = LBound(aStrings,2) To UBound(aStrings,2)
			If UCase(Left(aStrings(i,j),4)) <> "TIRE" _
				And UCase(Left(aStrings(i,j),11)) <> "PERFORMANCE" _ 
				And Trim(aStrings(i,j)) <> "" Then 
					sRes = sRes & ", " & Trim(aStrings(i,j))
			EndIf 
		Next j
	Next i
	If Len(sRes) > 2 Then sRes = Right(sRes, Len(sRes)-2)
	myConcat = sRes
End Function

Just write something like =MYCONCAT(A1:AX1) in the cell and get the result

[mariosv]

There are a couple of calc functions to do it.

CONCAT and TEXTJOIN

Edited 2019-04-25

.

[Lupp]

CONCAT() can accept sequences (given as arrays). CONCATENATE() does not.
CONCAT() and TEXTJOIN() are available (and working) in V 5.4 or higher.

[mariosv]

You are right @Lupp, I was thinking in concat and referencing cancatenate.

[JohnSUN]

Of course @mariosv, before writing a macro, I tried a formula with TEXJOIN() and could not make it work properly.

{=TEXTJOIN(", ";1;IF(OR(LEFT(A1:AX1;4)="Tire";LEFT(A1:AX1;11)="Performance");"";A1:AX1))}

Maybe you can do it? If so, please tell me what I missed.

[mariosv]

@JohnSUM, maybe the issue is that AND and OR doesn’t work fine in array context.

[JohnSUN]

Yes that’s it! Thank you! IF((LEFT(A1:AX1;4)="Tire")+(LEFT(A1:AX1;11)="Performance");... works correctly