Concatenate a range of cells with a comma
Google SheetsGoogle Sheets Problem Overview
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.
Google Sheets Solutions
Solution 1 - Google Sheets
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)
Solution 2 - Google Sheets
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"))
Solution 3 - Google Sheets
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
Solution 4 - Google Sheets
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!
Solution 5 - Google Sheets
It can be as simple as =concatenate(A1, " ", B2, " ", C2, ...)