1

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

Improve this question
6
  • Did you try replacing =concatenate with & ?
    – pjmg
    May 12, 2020 at 13:57
  • @pjmg The OP mentioned they already tried that. By the way formula syntax should not be changed, but you could suggest a modified formula to improve readability as a partial answer, specially if that will point to OP and readers to the right direction.
    – Rubén ♦
    May 12, 2020 at 14:12
  • If you could suggest how I could rewrite the formula using & it would be appreciated as I suspect I was doing it incorrectly. I am fairly new to writing formulas.
    – user246475
    May 12, 2020 at 16:11
  • 1
    I noticed a similar question was resolved in this post but I tried to apply it and it did not work for me. I think I did it incorrectly, webapps.stackexchange.com/questions/115334/…
    – user246475
    May 12, 2020 at 16:19
  • 1
    Regarding how to "upload" an spreadsheet, see How to share a Google Sheets demo spreadsheet
    – Rubén ♦
    May 12, 2020 at 16:46

1 Answer 1

Reset to default
1

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
  )
)
Improve this answer
7
  • For those rows where there is a Yes in Column E, the formula works perfectly. For those that do not have a Yes in Column E, it says the cell content exceeds 50,000 characters. I suspect I am using concatenate for the wrong need.
    – user246475
    May 12, 2020 at 16:14
  • @Kate I edited my answer
    – Rubén ♦
    May 12, 2020 at 16:44
  • Thank you very much Rubén. That solved my question. Worked perfectly and I was able to adapt it to other sheets that were similar. How do I mark this as solved?
    – user246475
    May 14, 2020 at 7:06
  • Please read What should I do when someone answers my question?
    – Rubén ♦
    May 14, 2020 at 12:39
  • Hi Rubén, I was wondering if you knew what I would add to the above formula so I could limit the output to 128 characters. I am building a naming convention for which the maximum characters can be 128 characters.
    – user246475
    May 14, 2020 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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