Only concatenate cells with value

[justrying]

I have a problem when I try to chain together cells with separating them with commas.

For example, if I want to concatenate A2:D2 with separated commas, I use =CONCATENATE(TRANSPOSE(A2:D2)&", "). Like shown here:

	A​	B​	C​	D​	E​
1​	Picture 1	Picture 2	Picture 3	Picture 4	Pictures
2​	1.jpg​	2.jpg​	3.jpg​	4.jpg​	1.jpg, 2.jpg, 3.jpg, 4.jpg,

But if, for example, C2 and D2 are empty, I don't want commas to appear. In other words, only the cells that have a value must be chained together with comma seperator.
It would also be ideal not to get a comma after the last number as shown in E2 in the example above, if possible.
Problem shown here:

	A​	B​	C​	D​	E​
1​	Picture 1	Picture 2	Picture 3	Picture 4	Pictures
2​	1.jpg​	2.jpg​			1.jpg, 2.jpg, , ,

How can I solve this?

Thanks in advance.

 

[Eric W]

Try TEXTJOIN:

Book1
	A	B	C	D	E
1	Picture 1	Picture 2	Picture 3	Picture 4	Pictures
2	1.jpg	2.jpg		4.jpg	1.jpg, 2.jpg, 4.jpg
Sheet1

Cell Formulas
Range	Formula
E2	E2	=TEXTJOIN(", ",1,A2:D2)

 

[justrying]

Try TEXTJOIN:

Book1
	A	B	C	D	E
1	Picture 1	Picture 2	Picture 3	Picture 4	Pictures
2	1.jpg	2.jpg		4.jpg	1.jpg, 2.jpg, 4.jpg
Sheet1

Cell Formulas
Range	Formula
E2	E2	=TEXTJOIN(", ",1,A2:D2)

Thank you!

 

[justrying]

One more thing. Would it be possible for it to only combine cells that have more than, say, 5 characters?

 

[Eric W]

You can do something like this:

Book1
	A	B	C	D	E
1	Picture 1	Picture 2	Picture 3	Picture 4	Pictures
2	Rocket	Car	Bicycle		Rocket, Bicycle
Sheet1

Cell Formulas
Range	Formula
E2	E2	=TEXTJOIN(", ",1,IF(LEN(A2:D2)>5,A2:D2,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

With Excel 2019, you need to use Control+Shift+Enter.