Concatenating only filled cells

Question

I currently have a spreadsheet to keep track of scores in a card game. There can be between two and five players. I have the following:

| Players  |
|----------|
| Dave     |
| Paul     |
| John     |
|          |
|          |

At the moment I use:

= JOIN( " vs " ; C10:C14 )

But the problem is I then end up with Dave vs Paul vs John vs vs.

Is there a way to make it say Dave vs Paul vs John but if I had more players, Dave vs Paul vs John vs Rob with one formula?

Answer 1

Try TEXTJOIN:

=textjoin(" vs ",1,C10:C14)

The second parameter is set to 1 indicating that empty cells should be ignored. Zero will include those empty cells.

Answer 2

Yes, by FILTERing the array:

= JOIN( " vs " ; FILTER(C10:C14; NOT(C10:C14 = "") ))

Thus, the JOIN method will only operate on non-empty cells.

I have set up an example spreadsheet.

Also, check the Google Spreadsheets function list (search for FILTER).

Answer 3

Both of the solutions above work if there is at least one cell containing text. However:

= JOIN(" vs ",SPLIT(JOIN("%",C10:C14),"%",0))

Would return %%%% if C10:C14 were all empty and.

= JOIN( " vs " ; FILTER(C10:C14; NOT(C10:C14 = "") ))

Would return #N/A if C10:C14 were all empty.

However, you can slightly amend the first solution to replace the % signs with empty strings by wrapping the formula with the SUBSTITUTE function like so:

=SUBSTITUTE(
   JOIN(" vs ",SPLIT(JOIN("%",C10:C14),"%",0)),    // text_to_search
   "%",                                            // search_for
   ""                                              // replace_with
 )

_{(Shown on multiple lines for clarity)}

Answer 4

I've found another solution:

=JOIN(" vs ",SPLIT(JOIN("%",C10:C14),"%",0))

The % can be any symbol really that isn't present in the list, like a comma, or ampersand, or question mark.

Answer 5

textjoin(" vs ", true, C10:C14) true means here that if cell is empty, then ignore