Excel: Textjoin skip header if cell is blank-CodePudding

I want to use textjoin. My formula right now works until it finds a blank cell, the Header is still joined.

What I like to have is that when a cell is empty, the header is ignored.

My formula until now is this, yet I don't know how to proceed further.

=TEKST.JOIN("   ",TRUE,$A$2:$O$2&", "&A3:O3)

The desired outcome row 2 to 4:

Material VZ
Driver SW-12

And for row 5:

Material VZ
Model X4
Drive SW-12

Hoping someone can help me on the way.

I have tried searching stackoverflow, and approaching the formula on a different way

CodePudding user response：

I guess this is what you want, put in cell E2 the following formula:

=TEXTSPLIT(TEXTJOIN("",,MAP(A2:A5, B2:B5, C2:C5, LAMBDA(a,b,c, 
 IF(a<>"", A1&" "&a&",", "") & IF(b<>"", B1&" "&b&",", "") 
   & IF(c<>"", C1&" "&c&",", "")))),,",",1)

You can encapsulate the IF logic in a user LAMBDA function (ADD) as follow:

=LET(ADD, LAMBDA(title, x, IF(x<>"", title &" "& x &",", "")),
 TEXTSPLIT(TEXTJOIN("",,MAP(A2:A5, B2:B5, C2:C5, LAMBDA(a,b,c, ADD(A1,a) &
  ADD(B1,b) & ADD(C1,c)))),,",",1))

Here is the output: