CODEWITHSUNDEEP
google-sheets
GhostKU
GhostKU

Reputation: 2108

How to use ARRAYFORMULA with TEXTJOIN?

I have Google Sheet document and I use TEXTJOIN in it. I want to have my TEXTJOIN formula filled when i add new rows. But it doesn't work

enter image description here

So I tried to use ARRAYFORMULA. IT works with numbers:

enter image description here

But it still doesn't work with TEXTJOIN:

enter image description here

Any Ideas?

UPD: If I use & instead of TEXTJOIN it works but in fact i shoud use delimiter and I should avoid double delimiter. I shouldn't have any a--a or d-D-: enter image description here

Upvotes: 12

Views: 15762

Answers (3)

Adam Liskey
Adam Liskey

Reputation: 11

=MAP(B2:B8,LAMBDA(array,TEXTJOIN(" ",TRUE,INDEX(B2:F8,ROW(array)-1,))))

=MAP( rangeOfHowManyRows, LAMBDA( aName, TEXTJOIN(delimiter, TRUE, INDEX( fullRange, ROW(aName)-1, ) ) ) )

The use of INDEX makes spanning many columns much less painful.

Upvotes: 1

user13299383
user13299383

Reputation: 71

Sheets now has a MAP function that you can use instead of ARRAYFORMULA.

You use a formula like this in column C:

=MAP(A1:A,B1:B,LAMBDA(a,b,TEXTJOIN(" ",TRUE,{a,b}))

The lambda will be called for each row, with the value of column A and column B for the row passed as the first two params of the lambda function.

Upvotes: 7

ttarchala
ttarchala

Reputation: 4557

Giving multiple array ranges to Arrayformula, and trying to force it to evaluate it in a specific order, is a losing battle. Try something simpler instead:

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

enter image description here

P.S. Your animated illustrations do show a level of effort on your part to ask a nice question, but frankly my eyes hurt from watching 3 cursors simultaneously, I'd prefer just the formulas or static images.

Upvotes: 3

Related Questions