CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulastextjoin
Neji Yang
Neji Yang

Reputation: 29

Ranges within ArrayFormula in Google Sheets

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?

Upvotes: 1

Views: 1468

Answers (2)

player0
player0

Reputation: 1

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

Upvotes: 2

Marios
Marios

Reputation: 27350

This should do the trick:

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

explanation

Upvotes: 1

Related Questions