Google-sheets – Problem with CONCATENATE when used inside of arrayformula

concatenategoogle sheetsworksheet-function

I'm trying to update this formula so it will work inside of an 'arrayformula'.

This works on its own and when I fill it down.

=if(E2>0,CONCATENATE(B2, " | ",C2," | ",D2), "")

This does not work when rows are added to the spreadsheet.

=arrayformula(if(E2:E>0,CONCATENATE(B2, " | ",C2," | ",D2), ""))

It only repeats the values in row 2 over and over again.

How should I write 'b2' and 'c2' and 'd2' so they adjust to row3, 4, 5, and so on as the number of rows expand?

Best Answer

Please try:

=ArrayFormula(if(E2:E>0,B2:B&" | "&C2:C&" | "&D2:D,""))

I would have expected:

=arrayformula(if(E2:E>0,CONCATENATE(B2:B," | ",C2:C," | ",D2:D),""))

to produce the same results as above, yet it does not. Whereas normally & rather than CONCATENATE is just a matter of personal preference, it seems they are not full interchangeable. No mention of that at CONCATENATE.

However, Google Spreadsheets has CONCAT where "Equivalent to the & operator" is mentioned. This:

Returns the concatenation of two values.

So is a substitute for & ... provided for the concatenation of one 'pair' at a time. Hence:

=arrayformula(if(E2:E>0,(concat(concat(concat(B2:B," | "),concat(C2:C," | ")),D2:D)),""))

does produce the same results 'as above'.

Seems somewhat curious to me, as does M$'s deprecating of CONCATENATE in favour of CONCAT, released in Excel 2016 (though that does allow joining of more than two strings together).