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?

5 Answers 5



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

  • 5
    This is the best answer. It's shorter, simpler and allows you to ignore blank values.
    – rfgamaral
    Commented Jan 25, 2018 at 17:57
  • 1
    I always read everything to look for the best answer not just a "working answer" :)
    – rfgamaral
    Commented Jan 25, 2018 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
    Commented May 24, 2021 at 20:02

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


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
    Commented Jun 2, 2015 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 and acknowledge you have read our privacy policy.

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