CODEWITHSUNDEEP
excel
MaxB
MaxB

Reputation: 458

Combine Excel columns and filter out blanks

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.

Excel Example

Upvotes: 0

Views: 143

Answers (1)

Aniket Kariya
Aniket Kariya

Reputation: 1970

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!

Excel Formula for your solution

Upvotes: 2

Related Questions