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?


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



=textjoin(" vs ",1,C10:C14)
  • 5
    This is the best answer. It's shorter, simpler and allows you to ignore blank values. – rfgamaral Jan 25 '18 at 17:57
  • 1
    I always read everything to look for the best answer not just a "working answer" :) – rfgamaral Jan 25 '18 at 18:23
  • 1
    The middle argument is the boolean "Ignore_empty" so could perhaps more properly be written =TEXTJOIN(" vs ", TRUE, C10:C14). – Baxissimo May 24 at 20:02

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:

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

(Shown on multiple lines for clarity)

  • 1
    Please note: "above" really has no context in answers, since answers can be sorted in different ways. – ale Jun 2 '15 at 19:44

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.


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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.