ArrayFormula for CONCATENATE multiple cells in Google Sheets

0
votes

Hollo to developers, I am unable to join the text in ArrayFormyla in Google Sheets I have tried without the ArrayFormula that is successive but in Array it join all the Rows in a single cell where the formula placed; =ArrayFormula(CONCATENATE( IF(ISBLANK(L2:L),"",(L2:L)&" "&(M2:M)&CHAR(10)&CHAR(10)), IF(ISBLANK(P2:P),"",(P2:P)&" "&(Q2:Q)&CHAR(10)&CHAR(10)), IF(ISBLANK(T2:T),"",(T2:T)&" "&(U2:U)&CHAR(10)&CHAR(10)), IF(ISBLANK(X2:X),"",(X2:X)&" "&(Y2:Y)&CHAR(10)&CHAR(10)), IF(ISBLANK(AB2:AB),"",(AB2:AB)&" "&(AC2:AC)&CHAR(10)&CHAR(10)), IF(ISBLANK(AF2:AF),"",(AF2:AF)&" "&(AG2:AG)&CHAR(10)&CHAR(10))) )

1
google-sheetsgoogle-sheets-formulaformulaarray-formulas
Hi. As it's a very long formula, it would be very helpful if you shared a sheet containing it. For the moment I would try to delete CONCATENATE( ....) formula and see what happens as I see that use concatenation sign: & that should do the job. - Krzysztof Dołęgowski
Hi @KrzysztofDołęgowski thanks for your clue, I have used concatenation sign: & that works perfect and full fill my requirements. again thanks! - saeed anser
I have used as edited and may helpful for any concerned one! =ARRAYFORMULA( IF(ISBLANK(L2:L),"",(L2:L)&" "&(M2:M)&CHAR(10)&CHAR(10))&IF(ISBLANK(P2:P),"",(P2:P)&" "&(Q2:Q)&CHAR(10)&CHAR(10))&IF(ISBLANK(T2:T),"",(T2:T)&" "&(U2:U)&CHAR(10)&CHAR(10))&IF(ISBLANK(X2:X),"",(X2:X)&" "&(Y2:Y)&CHAR(10)&CHAR(10))&IF(ISBLANK(AB2:AB),"",(AB2:AB)&" "&(AC2:AC)&CHAR(10)&CHAR(10))&IF(ISBLANK(AF2:AF),"",(AF2:AF)&" "&(AG2:AG)&CHAR(10)&CHAR(10))) - saeed anser

1 Answers

0
votes

As mentioned on the first comment, using the concatenation sign: & did the job

Leaving answer from the comments as community wiki to make it easier to be discovered as an answer:

I have used as edited and may helpful for any concerned one!

Final formula:

=ARRAYFORMULA( IF(ISBLANK(L2:L),"",(L2:L)&" "&(M2:M)&CHAR(10)&CHAR(10))&IF(ISBLANK(P2:P),"",(P2:P)&" "&(Q2:Q)&CHAR(10)&CHAR(10))&IF(ISBLANK(T2:T),"",(T2:T)&" "&(U2:U)&CHAR(10)&CHAR(10))&IF(ISBLANK(X2:X),"",(X2:X)&" "&(Y2:Y)&CHAR(10)&CHAR(10))&IF(ISBLANK(AB2:AB),"",(AB2:AB)&" "&(AC2:AC)&CHAR(10)&CHAR(10))&IF(ISBLANK(AF2:AF),"",(AF2:AF)&" "&(AG2:AG)&CHAR(10)&CHAR(10)))