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.

JPV · Accepted Answer · 2017-08-07T19:57:27.953

129

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)

edited Aug 07 '17 at 19:57

answered Dec 13 '16 at 16:38

JPV

26,499
4
33
48

4

I had to use ; instead of , so it was =textjoin(", "; 1; c2:f) – sigmapi13 Jun 26 '18 at 17:15
3

@sigmapi13: the use of semi-colons/comma's depends on the locale of the user. – JPV Jun 26 '18 at 19:15
1

+1 for the TEXTJOIN (https://support.google.com/docs/answer/7013992?hl=en) approach. Simple and effective. (I'd suggest, for the 2nd parameter, using `true` instead of `1`, as it's more explicit/readable.) – Jon Schneider Oct 21 '21 at 14:51

score 2 · Answer 2 · answered Dec 24 '20 at 12:14

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"))

answered Dec 24 '20 at 12:14

half of a glazier

1,864
2
15
45

Amrul Ismail · Answer 3 · 2021-11-15T10:14:47.827

0

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

	A	B	C
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

edited Nov 15 '21 at 10:14

answered Nov 14 '21 at 01:20

Amrul Ismail

3
2

score -1 · Answer 4 · edited May 10 '18 at 19:32

-1

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!

edited May 10 '18 at 19:32

Robert Columbia

6,313
15
32
40

answered May 10 '18 at 19:29

eddierh

7
1

score -4 · Answer 5 · edited Oct 12 '20 at 23:45

-4

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

edited Oct 12 '20 at 23:45

Salvatore

10,815
4
31
69

answered Dec 29 '19 at 03:20

user2060451

2,576
3
24
31

Concatenate a range of cells with a comma

5 Answers5

Linked