Formula to show contents of multiple cells comma seperated in a single cell

[kyleno]

I am looking to use a dynamic formula to show contents of multiple cells comma separated in a single cell.

For example,

I have a range of 10 cells (horizontal) that may or may not contain a value. I would like the formula to simply show what values are in that range and update as data is added or removed.

Concatenate does not work quite right, as there are lots of extra commas left in the cell.

Please help.

 

[SQUIDD]

Hi

would something like this work for you??

=A1&","&B1&","&C1

or do you sometimes have blank cells

Is it only ever 10 cells

Dave

 

[SQUIDD]

or with blank cells, maybe?

=IF(A1<>0,A1&",","")&IF(B1<>0,B1&",","")&IF(C1<>0,C1&",","")&IF(D1<>0,D1&",","")&IF(E1<>0,E1&",","")&IF(F1<>0,F1&",","")&IF(G1<>0,G1&",","")&IF(H1<>0,H1&",","")&IF(I1<>0,I1&",","")&IF(J1<>0,J1,"")

dave

 

[kyleno]

This worked great! I just needed to add a space after each comma and it works perfectly. Thanks so much!

or with blank cells, maybe?

=IF(A1<>0,A1&",","")&IF(B1<>0,B1&",","")&IF(C1<>0,C1&",","")&IF(D1<>0,D1&",","")&IF(E1<>0,E1&",","")&IF(F1<>0,F1&",","")&IF(G1<>0,G1&",","")&IF(H1<>0,H1&",","")&IF(I1<>0,I1&",","")&IF(J1<>0,J1,"")

dave

 

[Marcelo Branco]

Or maybe...

=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1&" "&G1&" "&H1&" "&J1)," ",", ")

M.

 

[kyleno]

The substitute formula doesn't quite work right. If there are multiple words in a cell with a space a comma separates them.

Or maybe...

=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1&" "&G1&" "&H1&" "&J1)," ",", ")

M.

 

[AhoyNC]

If you have Excel 2016 take a look at the TEXTJOIN function.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	qw	ty		yu	gh		jk		qw,ty,yu,gh,jk
Sheet

 

[SQUIDD]

This worked great! I just needed to add a space after each comma and it works perfectly. Thanks so much!

Your very welcome.
Glad it worked for you.

Regards

dave.

 

[kyleno]

@AhoyNC This looks nice and simple, but I'm using 2013 right now. Thanks though!