Concatenating only filled cells

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?

TEXTJOIN* (unlike JOIN) can skip blank values by setting its ignore_empty argument to TRUE or 1:

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

* When TEXTJOIN's ignore_empty argument is 0 (or FALSE) it behaves the same as JOIN.

Yes, by `FILTER`ing 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`).

No Values in the Range

1. @Dan's approach

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

Returns `%%%%` when there are no values in the range, but wrapping the formula in the SUBSTITUTE function `SUBSTITUTE(formula,"%",)` returns an empty value instead when the range is empty:

`=SUBSTITUTE(JOIN(" vs ", SPLIT(JOIN("%", C10:C14), "%")),"%",)`
2. @Vidar S. Ramdal's approach

``````=JOIN(" vs ", FILTER(C10:C14, C10:C14<>""))
``````

Returns an `#N/A` error if there are no values in the range but wrapping the formula in the IFNA function `IFNA(formula)` returns an empty value instead when the range is empty:

`=IFNA(JOIN(" vs ", FILTER(C10:C14, C10:C14<>"")))`

I've found another solution:

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

The `"%"` can be replaced by any character (eg. `","` or `"&"` or `"?"`) the only requirement is that it is not a character that will also show up in the actual list. Emoji's are often safe choices `"👍"` or `"🙂"` etc.