Google Sheets: Remove comma between specific cells in an TEXTJOIN

Z C 0
Viewed 29

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

spreadsheet google-sheets
3 Answers

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)))
Z C0

Option 01

=TEXTJOIN(", ",1,
          TEXTJOIN(", ",1,C5:F8),
          TEXTJOIN(", ",1,C3,I5))&", "&
          TEXTJOIN(", ",1,I6:L9)&" "&
          TEXTJOIN(" ",1,O5:O8)

enter image description here

Option 02

Use this formula to replace the last set of commas

=REGEXREPLACE(B12, 
              REGEXEXTRACT(B12&"", " --ar.+?(,.+)"), 
              REGEXREPLACE(REGEXEXTRACT(B12&"", " --ar.+?(,.+)"), ",", ""))

enter image description here

Osm0

Try this simpler formula (based on your formula)

=INDEX(concatenate("signal code: ",
  TEXTJOIN(", ",1,C5:C8,C3,I5:I8) & " " 
& TEXTJOIN(" ", 1,I9, O5, O6, O7, O8)))
marikamitsos0
Related