Join all non-empty cells in row?-CodePudding

Here is an example version of a Sheet I have:

The first column (A) is where the function I hope to create will go. I need a formula that joins all the cells in the row but skips the cells that are empty. The image above shows the desired outcome. If I use JOIN() it will put a blank space in for each empty cell, so JOIN(", ",A2:D2) would give me Jade, ,Mariela, but I don't want that blank spot in there.

Is there a function to do this? TIA

CodePudding user response：

Use TEXTJOIN() instead JOIN() with ignore empty option TRUE.

=TEXTJOIN(", ",1,B2:I2)

CodePudding user response：

delete everything in A column and use in A2:

=INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(IF(B2:I="",,B2:I&",")),,9^9))), ",$", ))