Search code examples
google-sheetsgoogle-sheets-formula

Complex Cocatenation in gsheets

I have a table to entries where I am trying to concatenate few columns and the concatenation should not happen when any one of the required values are empty

Here is the spreadsheet. https://docs.google.com/spreadsheets/d/1lQUG4TmFTKghV8r6Gg3EilLx6zyuTkrNfnCatRVWp_U/edit#gid=189998773

I did try to use the formula =MAP(scan(,D5:D,I5:I,lambda(a,b,d,if(or(a="",b="",d="",and(e="",f="")),,if(f<>"",f,e)&" "&a&" "&b&" "&c))),D5:D,H5:H,I5:I,J5:J,K5:K,lambda(a,b,c,e,f,if(or(a="",b="",c="",and(e="",f="")),,if(f<>"",f,e)&" "&a&" "&b&" "&c)))

But not able to get the the desired output. Please help!

Solution

  • You can simplify your formula by omitting SCAN

    =MAP(D5:D,H5:H,I5:I,J5:J,K5:K,
      LAMBDA(dd,hh,ii,jj,kk,
       IF(OR(dd="",hh="",ii="",AND(jj="",kk="")),,dd&"-"&hh&"-"&ii&"-"&if(kk<>"",kk,jj))))

    (for future reference: try naming your LAMBDAs accordingly)

    enter image description here