Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
1  
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

3 Answers 3

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.

share|improve this answer
    
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’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.

share|improve this answer
1  
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

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.