Join multiple columns horizontally into one column with ArrayFormula

Question

Sheet:

A	B	C	D	E
a	b	c	d	e
f	g	h	i	j
k	l	m	n	o

Currently to join the values horizontally of the columns, I do like this:

=ARRAYFORMULA(IF(A1:A="","",A1:A&B1:B&C1:C&D1:D&E1:E))

Output:

F
abcde
fghij
klmno

But now I came across the need to join the values of 100 columns, manually it's a huge formula, so I'm looking for a way to set the range to A1:CV, but JOIN and CONCATENATE don't support this type of multicolumn range.

Answer 1

try:

=FLATTEN(QUERY(TRANSPOSE(A:E);;9^9))

if you dont need extra spaces remove them like:

=INDEX(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(A:E);;9^9)); " "; ))

if columns contain words with spaces and you want to keep only those use:

=INDEX(SUBSTITUTE(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(
 SUBSTITUTE(A:E; " "; "×"));;9^9)); " "; ); "×"; " "))