Google sheet arrayformula join() and split() functions

8
votes

Does anybody know how to arrayformula this join function?

My formula is not as complex as the example here. ArrayFormula a Filter in a Join (Google Spreadsheets)

It does not contain a filter function, so I'm not sure what from that answer applies and doesn't apply.

I want to array formula this: =if(isblank(B2),,join("," ,B2:I2))

Using the normal way to array something doesn't work:

=ArrayFormula(if(isblank(B2:b),,join(",",B2:b:I2:i)))

Also for splits, I have split(B2, ",")

=ArrayFormula(split(B2:B,",")) does nothing but the first row

1
google-sheets

1 Answers

6
votes

Maybe try:

=ArrayFormula(if(len(B2:B), B2:B&C2:C&D2:D&E2:E&F2:F&G2:G&H2:H&I2:I,))

or

=ArrayFormula(substitute(transpose(query(transpose(B2:I),,rows(B2:B)))," ",""))

or, in case you want a space between the concatenated values:

=ArrayFormula(trim(transpose(query(transpose(B2:I),,rows(B2:B)))))

For using split() in arrayformula a workaround can be found here