31

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

38

Try TEXTJOIN:

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

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

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

0
6

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:

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

(Shown on multiple lines for clarity)

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

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.

1
0

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

1

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.