Generate a comma-separated list of cell contents, excluding blanks

Solution 1:

In Excel 2016, you can use TEXTJOIN:

=TEXTJOIN(delimiter,ignore_blanks?,values_to_be_joined)

So, in your case, you would use this formula:

=TEXTJOIN(",",TRUE,A2,C2,D2,F2)

Solution 2:

As you said, nested IF's aren't your cup of tea, but I can't see any other way to do this, without the VBA solution pointed by @nixda.

For the solutions I present I assume your data is like the following:

 |    A    |
-+---------+
1| Matthew |
2| Mark    |
3| Luke    |
4| John    |

I had a solution that did what you wanted but became "buried". I understood it after reading the comments you left in a recent answer (specially the comment about the fact you always have the first cell filled). This allows to not exist a nest, as the first condition (in the reasoning below) is always false.

=CONCATENATE(A1;IF(ISBLANK(A2);"";"," & A2);IF(ISBLANK(A3);"";"," & A3);IF(ISBLANK(A4);"";"," & A4))

You can always drop the CONCATENATE and replace the formula with ampersands, as pointed in the answer by @Scott. At this point it is simply a cosmetic issue.

=A1 & IF(ISBLANK(A2);"";"," & A2) & IF(ISBLANK(A3);"";"," & A3) & IF(ISBLANK(A4);"";"," & A4)

The first value is always written, so the only thing needed is to check if the active cell is blank and add a comma behind it if it isn't.

This way you don't need any helper cells, it's all in a single function.

I also wrote a more elaborate version, because I assumed the general term (i.e. that there could be a situation where you didn't fill your first cell). It only requires a 2-level nest, because there are at most 3 conditions that need to be checked:

• Are the cells before the active cell empty?
• Is the active cell empty?
• Is none of the above true? (defaulting condition)

The formatting needed, assuming you started the reasoning from the beginning, is the following:

• If yes, then don't place a comma before the cell data. If no, proceed.
• If yes, then don't place anything (an alternate solution would be to place the cell).
• If none is true, place a comma behind the cell data.

As such, here is the formula I used. I assumed all data was in a column, to use in a row change some formulas and the range.

=CONCATENATE(A1;IF(ROWS(A1:A1)=COUNTBLANK(A1:A1);A2; IF(ISBLANK(A2);"";"," & A2));IF(ROWS(A1:A2)=COUNTBLANK(A1:A2);A3; IF(ISBLANK(A3);"";"," & A3));IF(ROWS(A1:A3)=COUNTBLANK(A1:A3);A4; IF(ISBLANK(A4);"";"," & A4)))

ROWS counts the number of rows in any given interval. If it equals the number of blank cells behind the active cell, then it means all cells behind the active cell are blank, so no comma should be placed before.

The interval in ROWS and COUNTBLANK are the cells behind the active cell.

Solution 3:

If you’re willing to use some “helper” cells for intermediate values, you might like this:

Set cell AA2 (or Sheet2!A2, or wherever you want to put it) to

=IF(A2="", "", A2&",")

(where x & y is just a shorter way of saying CONCATENATE(x, y)), and set cells AC2, AD2, and AF2 similarly.  Set AZ2 to

=AA2 & AC2 & AD2 & AF2

Then your final result is

=IF(AZ2="", "", LEFT(AZ2, LEN(AZ2)-1))

Explanation:

The AA-AF cells append commas to the non-blank values.  So, using your example,

Matthew         Mark            Luke            John

would result in

Matthew,        Mark,           Luke,           John,

while

Jesus                           Mary            Joseph

would result in

Jesus,                          Mary,           Joseph,

(note the lack of a comma in Column C). 

AZ2 is a simple concatenation of the above: Jesus,Mary,Joseph,, so we’ve eliminated the extra comma after Jesus but added one at the end.  LEFT(AZ2, LEN(AZ2)-1) is all of AZ2 except for the last character.  And we need to test whether AZ2 is null to avoid getting an error if all four input cells are empty.