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

To concatenate a certain four cells, I'd use:

``````=CONCATENATE(A2,",",C2",",D2,",",F2)
``````

This would make it so that...

• A2 = "Matthew"
• C2 = "Mark"
• D2 = "Luke"
• F2 = "John"

would result in `Matthew,Mark,Luke,John`.

But we run into problems with something like...

• A2 = "Jesus"
• C2 = ""
• D2 = "Mary"
• F2 = "Joseph"

which would result in `Jesus,,Mary,Joseph`.

Here, the extra comma is undesired. Is there a way to gracefully handle this so that all non-blank cells are included in the comma-separated list, while avoiding the addition of unnecessary commas when some cells are blank?

Certainly, this can be done with a fair number of nested IFs, but I really want to avoid that if possible. Can it be done with native Excel functions, or perhaps an array formula? Or would one have to resort to VB Script for something like this?

-
Here is the VBA solution, but I'm looking forward for a non-VBA solution too. The accepted answer does work for your example as you can see here – nixda Sep 19 '13 at 19:53

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
``````

``````=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.

-
The nice thing about "use some 'helper' cells" solutions is that usually the formulas for the 'helper cells' can be written right into the formula for the target cell. I took your idea and integrated it all into one formula. `=CONCATENATE(S2,IF(U2="","",", "&U2),IF(V2="","",", "&V2),IF(W2="","",", "&W2))` One thing I forgot to mention is that the first cell in my list (S2 in this example) is going to be mandatory. So, by accepting that the first cell will always be filled, we can move the commas to the front of each subsequent string and avoid having an excess comma at the end. – Iszi Sep 20 '13 at 14:44
Note: While this solution does still require IF statements, please note that my original intent was to avoid nested IFs. Here, the IFs don't need to be nested, and there's no real repetition involved - there's only one IF statement for each cell that needs to be concatenated. So, unless there's a better solution that involves some sort of loop or array, this is definitely an acceptable answer. – Iszi Sep 20 '13 at 14:47

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.

-
Does the Portuguese version of Excel use semicolons to separate function arguments? It's usually a comma for English. – Iszi Sep 20 '13 at 19:02
Regional Preferences (in Windows) in Portuguese-speaking countries make Excel use semicolons as argument separators (because we use commas for decimal point). If it bothers you I can edit my answer. – Doktoro Reichard Sep 20 '13 at 19:03

If you want a general solution, the best answer is going to involve VBA.

If your example data is at all close to the full problem you're addressing, you can simply do the following to remove double-commas:

``````=SUBSTITUTE(CONCATENATE(A2,",",C2",",D2,",",F2),",,",",")
``````

The above will only work if you only ever have gaps one value wide. You'll need nested substitute() formulas to handle larger gaps.

``````=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(A2,",",C2,",",D2,",",F2),",,,,,,,,,,,,,,,,",","),",,,,,,,,",","),",,,,",","),",,,",","),",,",",")
``````

The above will reduce up to 398 sequential commas to one. It'll also reduce a lot of values above 399, but 399 itself and several others will result in more than one comma.

-
The last formula is quite a big nest... and if following is right, you're replacing 8 commas to 1, then 4 to 1 then 3 to 1, then 2 to one. I understand the reasoning behind the 3 (it's the next odd number after 1) Don't get where you will reduce those potential 398 commas, unless `SUBSTITUTE` searches for all separate matches inside the string. – Doktoro Reichard Sep 20 '13 at 18:18
`SUBSTITUTE` does replace all matches. 398 (24 * 16 + 14) commas get reduced to 38 commas, 38 (4 * 8 + 6) to 10, 10 (2 * 4 + 2) to 4, 4 (1 * 3 + 1) to 2, and 2 to 1. – Dane Sep 20 '13 at 18:39
Eww. Nested SUBSTITUTEs really aren't all that much better than nested IFs. – Iszi Sep 20 '13 at 18:39
@iszi, just how many blank cells are you dealing with? The nesting is only required if you want to handle a lot. If you don't want nested anything, you're going to need a user-defined formula. – Dane Sep 20 '13 at 18:42
@Dane I'm not trying to avoid "nested anything", but I do want to avoid huge amounts of multi-layered nesting especially when nesting within the same function. The sheet I'm working with right now only has 4 cells that need to be concatenated. The first cell is mandatory - so if that's empty there's something that needs fixing to begin with - but the other three may be filled or blank. – Iszi Sep 20 '13 at 18:50

1. Go to any spare column, I'll use B in this example
2. In Cell B2 enter simply =A2
3. In cell B3 enter =IF(A3="",B2,CONCATENATE(B2, ",", A3)
4. Copy down column B from B3 (not B2!) to the last row containing data in Column A

Voila, your comma-separated list is in the last cell in Column B :)

I came here looking for a solution, found none and so came up with the above. My data has 1000 rows, so manually adding to the concatenate isn't an option.

-

Just combine Concatenate() with if() in an array function:

``````=arrayformula(concatenate(if(not(isempty(a2:f2)),a2:f2&", ","")))
``````

This function will leave a comma at the end of your list, so if you need to avoid that and you know exactly where the end of the list is (in your example, column f) just handle that one separately:

``````=arrayformula(concatenate(if(not(isempty(a2:d2)),a2:d2&", ","")))&if(not(isempty(f2)),f2,"")
``````

Cheers!

-
the first formula gives #NAME? error – Prasanna Sep 30 '15 at 6:53