Formula (or vba) to place all the data inside column in a single cell

Hello there :wink:

I’ve the following scenario:





What I need is a formula or a macro to create in cell 2: ‘Data1’,‘Data2’,‘Data3’

I’ve done (found online) this in Excel:

Function transposeRange(Rg As Range)

Dim xCell As Range

Dim xStr As String

For Each xCell In Rg

    If Not IsEmpty(xCell.Value) Then

        xStr = xStr & "'" & xCell.Value & "',"

    End If


transposeRange = Left(xStr, Len(xStr) - 1)

End Function

But, as it seems, Range is not a valid type in Libreoffice.

Any way to do this?
I’m searching for something that can be compatible with botht the systems or, in alternative, something that does this work in Libreoffice.

Thanks in advance for any reply!


Why not TEXTJOIN() function? Something like as ="'"&TEXTJOIN("','",1,A2:A4)&"'"?

There is the TEXTJOIN() function to do it.