Concatenate a range of cells with a comma

Question

I'd like to combine a range of cells of data so that it comes out with just one text string and a comma + space between each one. I have been successful in using concatenate:

=ArrayFormula(concatenate(C3:F&", "))

but there are extra commas that don't need to be there in between some data and a lot of extra commas at the end.

Example Sheets.

Answer 1

A easy way (if you don't have that many columns) would be to use a literal array and filter out the empty cells with query. Something like this

=join(", ", query({C3:C; D3:D; E3:E; F3:F}, "where Col1 <>''"))

Also see cell A1 in the spreadsheet you shared.

UPDATED: An alternative (and shorter) way would be to use textjoin()

=textjoin(", ", 1, C2:F)

Answer 2

To prevent the delimiter doubling when there's a blank cell in the column, use

=JOIN(",", QUERY(A:A, "SELECT A WHERE A IS NOT NULL"))

Answer 3

You can also concat twice, with another column containing just a comma with spacing

	A	B	C	D	E
1	x	,	p
2	y	,	q
3	z	,	r

In D =ArrayFormula(concat(A1:A,B1:B) which will get x,
In E =ArrayFormula(concat(D1:D,C1:C) which will get x, p
You can now hide column D

Answer 4

For mine, I had a single number in each multiple cells which I wanted to combine into a single cell separated by a comma then a space. I used "=Concatenate (B2,"' ",B3,"' ",B4,"' ",B5,"' ",B6,"' ",B7,"' ",B8...), etc. It worked like a charm! Just type out all your cell names w/o anything in between, then use your arrow keys to advance the pointer, then paste ,"' ", between each one!

Answer 5

It can be as simple as =concatenate(A1, " ", B2, " ", C2, ...)