Issue

This Content is from Stack Overflow. Question asked by Z C

I’m using an ARRAYFORMULA / TEXTJOIN formula in Google Sheets to pull selected data together to make a single line of code arranged in a specific way for my project.

The resulting array needs to both INCLUDE commas in the first half, as well as EXCLUDE commas towards the end of the same formula.

example of commas needing removed

I’m currently using a ", " at the beginning of my TEXTJOIN, which works for placing a , between each cell, however I also need the last few cells (in this case: I9, O5, O6, O7, O8) to not have any commas between them.

Is there a way to do this?

Thank you in advance!

Here is a demo of what I’m working on:
https://docs.google.com/spreadsheets/d/1gTQiNKy4c376FuIWQQAomlJ6J1utCOjuq6JzRplTSu4/edit?usp=sharing

Solution

I got the answer from Reddit:

If your formula is:

=ARRAYFORMULA(concatenate("signal code: ",TEXTJOIN(", ",TRUE,
  $C$5:$F$5,$C$6:$F$6,$C$7:$F$7,$C$8:$F$8,$C3,$I5,$I6:L6,
  $I7:L7,$I8:L8,$I9,$O5, O6, O7, O8)))

Then just change the separator (in this example, a space) for those
few:

=ARRAYFORMULA(concatenate("signal code:",TEXTJOIN(",",TRUE,
  $C$5:$F$5,$C$6:$F$6,$C$7:$F$7,$C$8:$F$8,$C3,$I5,$I6:L6,$I7:L7,
  $I8:L8)& " " & TEXTJOIN(" ", TRUE, $I9, $O5, O6, O7, O8)))

This Question was asked in StackOverflow by Z C and Answered by Z C It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.