Combine Excel columns and filter out blanks

Question

I am trying to combine several excel columns and separate the data with a comma. Some cells are blank so I want to avoid adding unnecessary commas. How do I add a conditional that says "if the cell is blank don't attempt to add the data and another comma"? The output I am looking for is on the right. I am trying to perform this on a large dataset and the number of columns for each row is variable. The only time a blank cell will be encountered is at the end of the row.

I have tried basic concatenate and am using excel 2010.

What have you tried? What version of Excel do you have? If it's newer, you can use [`TEXTJOIN()`](https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c). — BruceWayne, Jan 31 '19 at 15:40
@BruceWayne check update, using excel 2010 and have only tried to use CONCATENATE() — MaxB, Jan 31 '19 at 15:43
If you don't have access to TEXTJOIN -- the easiest approach would be to write a VBA function. — John Coleman, Jan 31 '19 at 15:43
Check out [@Jeeped's excellent UDF](https://stackoverflow.com/a/50719050/4650297) — BruceWayne, Jan 31 '19 at 15:59

score 2 · Accepted Answer · answered Jan 31 '19 at 16:56

just copy and paste the following formula in your result column.

=IF(ISBLANK(A1),"", CONCATENATE(A1,IF(ISBLANK(B1),"", CONCATENATE(CONCATENATE(",",B1),IF(ISBLANK(C1),"", CONCATENATE(CONCATENATE(",",C1),IF(ISBLANK(D1),"", CONCATENATE(",",D1))))))))

This works fine for your first 4 columns (atleast in Excel 2016:), however this is not a recommended solution as you have mentioned that you're working with a large dataset, but might help you!

Combine Excel columns and filter out blanks

1 Answers1