Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I currently have a spreadsheet to keep track of scores in a card game. There can be between 2 and 5 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?


share|improve this question
up vote 9 down vote accepted

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

share|improve this answer
This is great - thank you! – Djave Feb 5 '13 at 16:25

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.

share|improve this answer
Nice alternative !! – Jacob Jan Tuinstra Apr 17 '14 at 7:31

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)

share|improve this answer
Please note: "above" really has no context in answers, since answers can be sorted in different ways. – Al E. Jun 2 '15 at 19:44

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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