[Issue] Concatenating Arrays

[Yacwroy]

Is there any way to concatenate arrays portably?

=CONCATENATE(A1:D1) doesn't concatenate A1,B1,C1,D1 it just returns the first non-empty cell (or something like that).

I'd like to use [E1]: "=CONCATENATE(OFFSET($B$2, 0, 0, $D1))"

Thanks.

[acknak]

Yes, it's baffling to me why CONCATENATE() cannot process a range of cells. Even the OpenDocument Formula spec has not changed this silly limitation. Even more useful would be a join() function that would take a list or range and join them all together with a separator string, but hey, "this is a spreadsheet, not a programming language."

You can write a BASIC macro easily enough to step through all the cells in a range and stick them together, but you probably ought to ask about that in the macros forum.

[Yacwroy]

I just used the next-door row to do it.

[A1]..[A(N)] are strings to be concatenated.

[B1]=A1
[B#]=CONCATENATE(A#,B(#-1)) where # is 2..N
then use [B(N)] as the concatenated result.

not very efficient tho, but it doesn't use macros or anything.

[Hagar Delest]

You should subscribe and vote for it (up to 2 votes per issue) : Issue 84516 - CONCATENATE an Array.

I tag the thread as Issue.

[Villeroy]

Code: Select all

Function VVJOIN(v(), optional sep)
REM join a *v*ertical *v*vector with optional separator (default "")
REM use with TRANSPOSE for horizontal vectors
On Error Goto NullErr
	If isMissing(sep) then sep = ""
	iLB = lBound(v(), 1)
	iUB = uBound(v(), 1)
REM convert from 2D array to flat array:
	Dim a(iLB to iUB)
	For i = iLB to iUB
		a(i) = v(i, 1)
	next
	VVJOIN = join(a(), sep)
exit function
NullErr:
REM return #VALUE
	VVJOIN = Null
End Function

Install
Menu:Tools>Macros>Organize>Basic... Container:"MyMacros", Library:"Standard", <any (new) module>
Copy into library "Standard" of the respective document to make it portable.

Examples
=VVJOIN(A1:A9) [vertical vector without spearator]
=VVJOIN(TRANSPOSE(A1:G1)) [horizontal vector without spearator]
=VVJOIN(A1:A9;" ") [vertical vector separated by space]
=TRIM(VVJOIN(A1:A9;" ")) [same as above with leading and trailing spaces removed]
=TRIM(VVJOIN(TRANSPOSE(A1:A9);" ")) [same as above horizontaly]

[eddyparkinson]

STRJOIN

=STRJOIN(A1:E1,",")

will give you 5,4,5

or:

=STRJOIN(A1:E1,",","(",")")

will give you (5),(4),(5)

Code: Select all

Function STRJOIN(range, Optional delimiter As String, Optional before As String, Optional after As String)
    Dim row, col As Integer
    Dim result, cell As String

    result = ""

    If IsMissing(delimiter) Then
        delimiter = ","
    End If
    If IsMissing(before) Then
        before = ""
    End If
    If IsMissing(after) Then
        after = ""
    End If

    If NOT IsMissing(range) Then
        If NOT IsArray(range) Then
            result = before & range & after
        Else
            For row = LBound(range, 1) To UBound(range, 1)
                For col = LBound(range, 2) To UBound(range, 2)
                    cell = range(row, col)
                    If cell <> 0 AND Len(Trim(cell)) <> 0 Then
                        If result <> "" Then
                            result = result & delimiter
                        End If
                        result = result & before & range(row, col) & after
                    End If
                Next
            Next
        End If
    End If

    STRJOIN = result
End Function

Allows optional choice of the delimiter (defaults to ","), and the strings which go before and after each non-blank entry in the range (default to "").


Examples:

Code: Select all

  =STRJOIN(C3:C50)

replacing C3:C50 with the range of strings you want to join.

To customise the delimiter, instead use something like:

Code: Select all

  =STRJOIN(C3:C50; " / ")

If you wanted to join a bunch of email addresses, you could use:

Code: Select all

  =STRJOIN(C3:C50; ", "; "<"; ">")

and the result would be something like

Code: Select all

<foo@bar.com>, <baz@qux.org>, <another@email.address>, <and@so.on>

from :
https://stackoverflow.com/questions/182 ... 09#2417109

[Lupp]

For compatibility with the 2016/365 version of Excel LibreOffice implemented a function TEXTJOIN starting with V5.2. The function needed some debugging but seems to work well now (V5.3.3 or higher) in most cases. It has two parameters in front of a series of parameters taking the texts (ranges allowed): The first one defines the delimiter to use, the second one decides how empty elements shall be handled.

(I wrote a pair of related functions capable in addition of suppressing numbers [either 0 or all] and/or repetitions. If you are interested, tell me.)