CODEWITHSUNDEEP
google-sheetsconcatenationarray-formulastextjoin
Yu_Jain
Yu_Jain

Reputation: 161

Google Sheet use ArrayFormula with textjoin

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?

Upvotes: 1

Views: 1438

Answers (3)

Aresvik
Aresvik

Reputation: 4620

Try this in cell L1:

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

enter image description here

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,}",", "),"[,\ ]+$",)})

Upvotes: 2

Mike Steelson
Mike Steelson

Reputation: 15308

simple but it remains a comma at the end, in L2

=flatten(query(transpose(arrayformula(if(B2:K="","",B2:K&", "))),,COLUMNS(B2:K)))

Upvotes: 1

GoranK
GoranK

Reputation: 1668

You cannot use textjoin inside arrayformula.

Here is the custom function you can use instead:

function array_text_join(data) {
  const result = data.map(row=>{
    const isEmptRow = !row.some(cell=>cell !== "")
    return [isEmptRow? undefined: row.filter(cell=>cell!=="").join(",")]
  })
  return result
}

Just place it into L2 =array_text_join(A2:K)

Upvotes: 2

Related Questions