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

Use TEXTJOIN instead of JOIN

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.

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

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<>"")))

Answer 4

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.