# Concatenating only filled cells

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?

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.

• This is the best answer. It's shorter, simpler and allows you to ignore blank values. Jan 25, 2018 at 17:57
• I always read everything to look for the best answer not just a "working answer" :) Jan 25, 2018 at 18:23
• The middle argument is the boolean "Ignore_empty" so could perhaps more properly be written =TEXTJOIN(" vs ", TRUE, C10:C14). 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:

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

(Shown on multiple lines for clarity)

• Please note: "above" really has no context in answers, since answers can be sorted in different ways.
– ale
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.

• Nice alternative !! Apr 17, 2014 at 7:31

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