Combining texts but ignore blanks

Lulu B

How do I combine texts but ignore the cells that are blank?

This is what I currently have:

=[Column2]1 + ", " + [Column3]1 + ", " + [Column4]1 + ", " + [Column5]1 + ", " + [Column6]1 + ", "

Jeff Reisman

@Lulu B

Maybe try a series of IFs. The basic logic is IF the cell in column 2 on this row has a number value in it, list it here plus a comma and space, if it's not a number then put no value here; then check if column 3 is a number value, and so on.

=IF(ISNUMBER([Column2]@row), ([Column2]@row + ", "), "") + IF(ISNUMBER([Column3]@row), ([Column3]@row + ", "), "") + IF(ISNUMBER([Column4]@row), ([Column4]@row + ", "), "") + IF(ISNUMBER([Column5]@row), ([Column5]@row + ", "), "") + IF(ISNUMBER([Column6]@row), [Column6]@row, "")

Change ISNUMBER to ISTEXT if you're checking for a text value vs a number value. Or, if you have a mix of data types, use ISBLANK and change the order around, so that IF the cell is blank, list nothing, if it's not blank, then list the cell value plus the comma and space:

=IF(ISBLANK([Column2]@row), "", ([Column2]@row + ", ")) +

This works on my test sheet:

Kelly Moore

Hey @Lulu B

As an alternate approach to Jeff's approach above, one can try this

=JOIN(COLLECT([Column 2]@row:[Column 6]@row, [Column 2]@row:[Column 6]@row, <>""),",")

Jeff Reisman

@Kelly Moore

That is far more elegant than my solution! 👍️🥇