Join multiple columns horizontally into one column with ArrayFormula

user_11462274 0
Viewed 74

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.

google-query-language google-sheets-formula flatten transpose google-sheets
1 Answers

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)); " "; ); "×"; " "))
user_56326290
Related