How to combine a range of values into one cell, with a character in between them

If you have a list of items in a spreadsheet, set up like a table, then you can use the join function to combine the values and display them all together in a horizontal list, with some characters (eg a comma and a space) in between tehm..

For example, in my summer-school management spreadsheet I have a list of teachers in column C, currently in rows 1 (heading) to five.

 Teachers
-----------
 Fiona
 Patrick
 Sean
 Lynne


To put the teacher-names into the one cell, nicely formatted with a comma and a space between each one, I could use the JOIN() function, like this:

= JOIN( ", " ; C2:C5 )

But there are two problems:

• The result is "Fiona, Patrick, Sean, Lynne, " - there is an extra "comma space" at the end

• If the number of teachers changes, the I have to adjust the formula

A better option it to also use the FILTER() function, to remove values that you dont't want in this case, blanks which are represented by "".

So the formula becomes:

 = JOIN( ", " ; FILTER(C2:C9999; NOT(C2:C999 = "") )) 

This says to join all the values in cells C2 through to C9999 together, to put a comma and space between each one, but to leave out any that are blank,

Using this, my list becomes "Fiona, Patrick, Sean, Lynne", and I can add up to 9998 names in the Teachers column and it still works.

Extra for Experts

If I had used named ranges, and so didn't have a heading at the top of the column, then the formula could become even more flexible, with no limit to the number of rows included, like this:

= JOIN( ", " ; FILTER(C:C; NOT(C:C = "") ))
or

= JOIN( ", " ; FILTER(Teachers; NOT(Teachers = "") ))