Ask Your Question
0

How to concatenate 50 columns in libreoffice using macros?

asked 2019-04-24 13:47:21 +0200

varun gravatar image

updated 2019-04-24 14:23:53 +0200

JohnSUN gravatar image

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
edit retag flag offensive close merge delete

Comments

1

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 gravatar imageJohnSUN ( 2019-04-24 14:49:16 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2019-04-24 23:08:16 +0200

m.a.riosv gravatar image

updated 2019-04-25 16:15:21 +0200

There are a couple of calc functions to do it.

CONCAT and TEXTJOIN

Edited 2019-04-25

image description .

edit flag offensive delete link more

Comments

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

Lupp gravatar imageLupp ( 2019-04-25 00:15:51 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2019-04-25 09:12:52 +0200 )edit

Of course @m.a.riosv, 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.

JohnSUN gravatar imageJohnSUN ( 2019-04-25 13:12:59 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2019-04-25 16:14:06 +0200 )edit

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

JohnSUN gravatar imageJohnSUN ( 2019-04-25 16:19:46 +0200 )edit
1

answered 2019-04-24 15:25:27 +0200

JohnSUN gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-04-24 13:47:21 +0200

Seen: 100 times

Last updated: Apr 25