# 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. Commented Jan 25, 2018 at 17:57
• I always read everything to look for the best answer not just a "working answer" :) Commented 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)`. Commented May 24, 2021 at 20:02

Yes, by `FILTER`ing 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
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