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?
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).
=JOIN(" vs ", SPLIT(JOIN("%", C10:C14), "%")) Returns %%%% when there are no values in the range, but wrapping the formula in the SUBSTITUTE function SUBSTITUTE(formula,"%",) returns an empty value instead when the range is empty:
=SUBSTITUTE(JOIN(" vs ", SPLIT(JOIN("%", C10:C14), "%")),"%",) =JOIN(" vs ", FILTER(C10:C14, C10:C14<>"")) Returns an #N/A error if there are no values in the range but wrapping the formula in the IFNA function IFNA(formula) returns an empty value instead when the range is empty:
=IFNA(JOIN(" vs ", FILTER(C10:C14, C10:C14<>""))) I've found another solution:
=JOIN(" vs ", SPLIT(JOIN("%", C10:C14), "%")) The "%" can be replaced by any character (eg. "," or "&" or "?") the only requirement is that it is not a character that will also show up in the actual list. Emoji's are often safe choices "👍" or "🙂" etc.
