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.
CodePudding user response：
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)); " "; ); "×"; " "))