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?
18 Answers
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)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.
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 & AF2Then 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 Johnwould result in
Matthew, Mark, Luke, John,while
Jesus Mary Josephwould 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.
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.
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.
7I'll assume your data has headers and starts in A2.
- Go to any spare column, I'll use B in this example
- In Cell B2 enter simply =A2
- In cell B3 enter =IF(A3="",B2,CONCATENATE(B2, ",", A3)
- 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.
0Just 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!
3Can'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
