I have multiple rows, each containing cells that do not overlap and I want to easily merge them into a single row with a formula. Bonus if I can also fill in the empty cells with something. In this case the text "off". All in a single formula.

CodePudding user response：

try:

=INDEX(TRIM(QUERY(B2:H7;;9^9)))

and:

=INDEX(IF(TRIM(QUERY(B2:H7;;9^9))=""; "off"; TRIM(QUERY(B2:H7;;9^9)))