Google Sheets – Array Formula with Concatenate Not Working

google sheetsgoogle-sheets-arrayformula

I have a Google Sheet where I am wanting to use an array formula so it auto copies down the column rather than the current formula which needs to be copied down manually.

=(IF($E2="Yes", $D2, CONCATENATE(IF(ISBLANK($A2),"", CONCATENATE($A2, " - ")), IF(ISBLANK($B2),"",CONCATENATE($B2, " - ")),IF(ISBLANK($C2),"",CONCATENATE($C2,". ")),$D2)))

I tried changing this to an array formula but the concatenation does not work. I have read other posts where the solution is to use the & function but I think I am doing it wrong.

The following is the array formula which does not work as it is saying the result is over 50,000 characters.

=ArrayFormula((IF($E2:$E="Yes", $D2:$D, CONCATENATE(IF(ISBLANK($A2:$A),"", CONCATENATE($A2:$A, " - ")), IF(ISBLANK($B2:$B),"",CONCATENATE($B2:$B, " - ")),IF(ISBLANK($C2:$C),"",CONCATENATE($C2:$C,". ")),$D2:$D))))

Best Answer

As your array formula uses row open references (i.e. $E2:$E), a simpler workaround might be to remove the blank rows below your data range.

If the above doesn't solve the problem then you have to split the result in smaller chunks, like instead of using a single formula use multiple formulas in such way that each formula results doesn't exceeds the cell content limit of 50,000 characters.

Regarding how to replace CONCATENATE by & to make your original formula works as an ARRAYFORMULA, try this

=ARRAYFORMULA(
  IF(
    $E2:E="Yes", $D2:D, 
      IF(ISBLANK($A2:A), "", $A2:A & " - ") &
      IF(ISBLANK($B2:B), "", $B2:B & " - ") &
      IF(ISBLANK($C2:C), "", $C2:C & ". ") &
      $D2:D
  )
)
Related Topic