Concatenate non empty cells in each row with arrayformula in google sheets

Question

I have a range of values and empty cells A2:F15 I want to concatenate only non empty cells using a single formula Arrayformula, Make a copy of this example sheet.

I created this formula but it's not an array based

=TEXTJOIN(", ",,TRANSPOSE(QUERY(FLATTEN(A2:F2)," where Col1 is not null ")))

player0 · Accepted Answer · 2022-08-16 20:11:28Z

1

use query smash:

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

edited Aug 16, 2022 at 20:11

answered Aug 16, 2022 at 19:58

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Víctor Enrich Over a year ago

Excellent solution. I have an extra question, though: what if, besides achieving what your formula does, we would want to add, for each non empty cell, the contents of its corresponding header. In the example the columns A to F there's a merged cell with "Input", but let's imagine there are 6 heather cells with "Input A", "Input B" etc. Also, joining them with a different separator than the comma, for instance ":". Let's say that in the first result we would get: Input A: nSM, Input D: Egk, Input F: kfPR, and in seond row in results: Input F: Xtv. Would that be possible? Thanks

Collectives™ on Stack Overflow

Concatenate non empty cells in each row with arrayformula in google sheets

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related