How to combine cells in a range only if not blank

[mljohn]

How do I create a formula that will combine only the cells in a range that are not blank.

I tried this formula =IF(K3="",J3,K3&", "&L3&", "&M3&", "&N3&", "&O3&", "&P3&", "&Q3&", "&R3&", "&S3&", "&T3&", "&U3)

but if K3 is not blank I get a comma for every blank cell. Example: 4T311Q, , , , , , , , , ,

How do I combine all of the non-blank cells j3:u3 ?


Thanks

Matt

 

[Firefly2012]

Hi Matt

Assuming the values in the cells don't already contain spaces you could consider using:

Excel 2010
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2
3									dog		cat		horse			zebra
4
5
6
7	dog, cat, horse, zebra
8
9
Sheet3

Cell Formulas
Range	Formula
C7	=IF(K3="",J3,SUBSTITUTE(TRIM(SUBSTITUTE(K3&","&L3&","&M3&","&N3&","&O3&","&P3&","&Q3&","&R3&","&S3&","&T3&","&U3,","," "))," ",", "))

 

[mljohn]

Works great,

Can you explain how the formula works?

Especially the SUBSTITUTE(TRIM(SUBSTITUTE part

Thanks

Matt

 

[Firefly2012]

Sure: most of your formula remains unchanged.

The inner Substitute converts all of the commas to spaces.

The Trim then removes consecutive spaces so that only a single one remains between words/values.

The outer substitute then converts these resultant spaces back to commas.

Make sense?

 

[Firefly2012]

Sure: most of your formula remains unchanged.

The inner Substitute converts all of the commas to spaces.

The Trim then removes consecutive spaces so that only a single one remains between words/values.

The outer substitute then converts these resultant spaces back to commas.

Make sense?

 

[s31064]

The easiest way to do this is this formula:

=SUBSTITUTE((TRIM(IF(C1<>"", " "&C1, "")&IF(D1<>"", " "&D1, "")&IF(E1<>"", " "&E1, "")&IF(F1<>"", " "&F1, ""))), " ", ",")

Obviously you need to adjust the cell references to meet your needs.

Basically what you have is an IF statement (with nested IF statements for each referenced cell) that will ignore empty cells (each IF is checking to make sure there's something in the referenced cell with <>"", and if there is, add a space to the front of that value in a text string), then the TRIM does its thing (removes all spaces except for a single space between the cell values, which in this case should only be the first space) and finally SUBSTITUTE changes the spaces to commas (that's the , " ", "," at the end), since you're looking for commas to delimit the values.

This is similar to what Firefly2012 posted, the difference is this formula never processes the blank cells to start with.