10

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?

1
13

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)
3
  • While this may answer the question, consider adding explanations to make it more understandable. This actually answer the question.
    – Vylix
    Apr 21 '17 at 14:46
  • 1
    This is by far the best and most efficient answer
    – Vylix
    Apr 21 '17 at 15:29
  • Also works in Google Sheets! 👍 support.google.com/docs/answer/7013992?hl=en Oct 18 at 20:34
5

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.

2
  • 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. Sep 20 '13 at 19:03
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.

2
  • 2
    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
2

I presume you are looking for a simple solution and not to improve your skills.

You can use an add-in if that is allowed (i.e. you have permission to install addins). ASAP Utilities is one that I highly recommend which is free for non-commercial usage.

Here is what you can do with one of their functions

This utility merges the data from the columns in your selection. For each row in your selection the data from the adjacent columns will be concatenated into the first cell in the row.

You can specify the following: Delimiter to put between the cell values Skip empty cells Use the value, formula or formatted value from the cells.

Screenshot

0
1

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.

7
  • 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. 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
  • 1
    The reason I said "avoid nested IFs" was because my initial thought was to do some form of =IF(AND(B2="",C2="",D2=""),A2,IF(AND(C2="",D2=""),CONCATENATE(A2,", ",B2) ... But, since you can nest an IF inside of CONCATENATE, it turns out I only really need one IF statement per cell to be concatenated - still a little less than ideal, as I'd prefer some form of FOR loop or an array function, but it works very nicely. See my comment to @Scott's answer.
    – Iszi
    Sep 20 '13 at 18:56
  • 1
    @Iszi Actually there's this post on StackOverflow (How to loop in excel without VBA or macros?) but you would need to have your data accurately formatted (done) and some helper cells. Sep 20 '13 at 19:01
1

I'll assume your data has headers and starts in A2.

  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.

0
0

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!

3
  • 1
    the first formula gives #NAME? error
    – Prasanna
    Sep 30 '15 at 6:53
  • 2
    This appears to be a Google Spreadsheets formula. Array formulas are specified by keystroke entry in Excel, not by a worksheet function.
    – Excellll
    Apr 21 '17 at 15:22
  • 1
    ARRAYFORMULA is a Google sheet function. In Excel you must use Ctrl+Shift+Enter
    – phuclv
    Jan 31 '19 at 1:52
0

Can't remember where I found this solution, but it works great for getting a list of strings.

For data in cells A1 to A3

=CONCATENATE(TRANSPOSE(A1:A3)

Press enter, then go back into the formula bar and highlight A1:A3 in the formula, then press F9. This will turn your range into a semi-colon delimited list with quotes around each item.

=CONCATENATE(TRANSPOSE({"Matthew", "Mark", "John"}))

After pressing F9, highlight and copy the list, then paste into another cell. From here you can find and replace to use commas instead of semi-colons or single quote instead of double.

1
  • 1. Welcome to Super User. Cool answer. However: 2. This is not very applicable to the question. For example, the querent's data is not in A1:A3. It would be more helpful if aimed specifically at the question being answered. 3. Your first formula has a syntax error. 4. You need not do any of this to take advantage of the F9 'trick'. Simply type =A1:A3 in a cell, F2 into the formula bar, F9 to evaluate. Your answer has the seed of a useful tip but is obfuscated by a lot of totally extraneous and confusing detail. 5. It's also a one-off point solution that doesn't really help going forward.
    – Alex M
    Apr 5 '19 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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