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?

  • 1
    Yes! Thank you and please answer it so I can approve it. Thanks again! – Mr. B Aug 1 '17 at 0:38

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.