Google Sheet use ArrayFormula with textjoin

Question

I have this sheet with a list of items. I want to concat the columns with products B:K into one column and to automatically run this function for all non-empty rows. I've tried to use arrayformula with textjoin, but it just copies the same rows multiple times. How can I do this?

Answer 1

Try this in cell L1:

=arrayformula({"Name","Items";A2:A,regexreplace(regexreplace(trim(flatten(query(transpose(B2:K)&",","",9^9))),"[\ ,]+$",),",\ ",",")})

Or this to not remove spaces after the commas:

=arrayformula({"Name","Items";A2:A,regexreplace(trim(flatten(query(transpose(B2:K)&",","",9^9))),"[\ ,]+$",)})

If you've got spaces in your product names, or gaps between products (horizontally), then use:

=arrayformula({"Name","Items";A2:A,regexreplace(regexreplace(trim(flatten(query(transpose(B2:K)&",","",9^9))),"[,\ ]{2,}",", "),"[,\ ]+$",)})