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

[marco.carolo]

Hello there

I’ve the following scenario:

Col1

Data1

Data2

Data3

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

Next

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!

Marco

[JohnSUN]

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

[mariosv]

There is the TEXTJOIN() function to do it.