Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

Thanks

share|improve this question
add comment

2 Answers 2

up vote 5 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
add comment

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 at 7:31
add comment

Your Answer

 
discard

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.