0

Is it possible to include ranges of ranges in arrayformulas in Google Sheets?

For instance, the normal formula is:

=TEXTJOIN(" ",TRUE,A1:C1)

This joins the cells in range A1:C1 with a space

However, I want this to repeat over the entire column, so something like:

=ARRAYFORMULA(TEXTJOIN(" ",TRUE,(A1:A):(C1:C)))

Is this possible in google sheets?

player0
  • 124,011
  • 12
  • 67
  • 124
Neji Yang
  • 9
  • 1
    For documentation purposes, please accept the answer if it helped you or give your input regarding the answer. – Marios Aug 24 '20 at 16:57

2 Answers2

2

use:

=ARRAYFORMULA(TRANSPOSE(QUERY(TRANSPOSE(A2:E);;9^9)))

enter image description here

or shorter:

=INDEX(FLATTEN(QUERY(TRANSPOSE(A2:E);;9^9)))

enter image description here

to skip empty cells do:

=INDEX(TRIM(FLATTEN(QUERY(TRANSPOSE(A2:E);;9^9))))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
0

This should do the trick:

=arrayformula(A1:A & " " & B1:B & " " & C1:C)

explanation

Marios
  • 26,333
  • 8
  • 32
  • 52