2

I'm looking to combine the text data different cells in Excel. I am currently using the formula:

=J3&", "&K3&", "&L3&", "&M3

When all my cells contain text, the outcome is exactly what I want.

However, in some cases, there are cells with no data in them. At the moment, my formula just returns " , , , " if there is no data in the cells. Is there a way to rewrite the formula so it ignores cells that don't contain any data?

3

With Office 365 Excel this becomes easy.

Office 365 Excel introduced TEXTJOIN()

=TEXTJOIN(", ",TRUE,J3:M3)

With TRUE as the second criterion, it will automatically skip all blanks.

NOTE: I know the OP state Excel 2010, this is for future readers.

0

Try adding an IF() statement to each cell:

=IF(J3<>"";J3;"")&(IF(K3<>"";", "&K3;""))&(IF(L3<>"";", "&L3;""))&(IF(M3<>"";", "&M3;""))

Notice the slight change in formula for the first IF() statement.

3
  • This will start the string with ; if J3 is blank, may want to add the ; to the J3 and use MID() to start at the second character to ensure that the ; does not get added. – Scott Craner May 15 '18 at 12:49
  • @ScottCraner no it wont. Hence my comment "Notice the slight change..." I fixed for that. However, if both J3 AND K3 are blank then it will start with a ; – Kevin Anthony Oppegaard Rose May 16 '18 at 6:05
  • sorry it will leave a , on the front if J3 is empty. See Here – Scott Craner May 16 '18 at 12:55
0

Try this:

=CONCATENATE(  IF(ISBLANK(J3),"",J3&", "),  IF(ISBLANK(K3),"",K3&", "),  IF(ISBLANK(L3),"",L3&", "),  IF(ISBLANK(M3),"",M3))

The Microsoft Excel ISBLANK function can be used to check for blank or null values.

Here ISBLANK() function is used to check if cell has a value. If there is no value then a blank "" string is added to result. Otherwise, cell value along with string ", " is added.

2
  • Thanks for your contribution. Can you explain this? :) – bertieb May 15 '18 at 13:04
  • You're on a right track, but concatenating the comma to a present value will leave an extra comma if there aren't values after that. – fixer1234 May 18 '18 at 20:39
0

Scott presents a neat solution for newer versions of Excel. The other three solutions (so far) use simple functions that work in any version of Excel. However, the delimiting commas are actually a little more complicated than those formulas handle. To accomplish this in that style of solution requires some logic for each comma.

Here are the possible combinations of present and missing values:

enter image description here

The first row is no missing values. The next four rows have one missing value in each possible position. The next six rows have two missing values in each possible position. Then the four rows of three missing values, and finally all values missing. To get a comma where it is needed, and only where it is needed, you need some logic for each comma.

The easiest way to do this is to associate the comma with the "next" value. At each comma position, there is no comma if no values came before it (which is a different case for each position). If there is any preceding value, you only want a comma if the next position has a value. So the comma logic looks like this:

First comma:   =IF(J3="","",IF(K3="","",", "))        or  =IF(AND(J3<>"",K3<>""),", ","")
Second comma:  =IF(J3&K3="","",IF(L3="","",", "))     or  =IF(AND(J3&k3<>"",L3<>""),", ","")
Third comma:   =IF(J3&K3&L3="","",IF(M3="","",", "))  or  =IF(AND(J3&K3&L3<>"",M3<>""),", ","")

I've put each comma in a separate column to illustrate the pattern with the various combinations of values:

enter image description here

The solution concatenates the values (or blank if none), with the commas. For readability, here's the formula with the comma inserted as a reference to its formula cell:

=IF(J3="","",J3) &N3 &IF(K3="","",K3) &O3 &IF(L3="","",L3) &P3 &IF(M3="","",M3)

Substituting the comma formulas into the result formula gives you:

=IF(J3="","",J3) &IF(AND(J3<>"",K3<>""),", ","") &IF(K3="","",K3) &=IF(AND(J3&k3<>"",L3<>""),", ","") &IF(L3="","",L3) &=IF(AND(J3&K3&L3<>"",M3<>""),", ","") &IF(M3="","",M3)

enter image description here

-2

Enter this formula into a blank cell where you want to get the Concatenated result.

=IF(LEN(TRIM(J3))=0,",",J3)&IF(LEN(TRIM(K3))=0,",",K3)& IF(LEN(TRIM(L3))=0,",",L3)& IF(LEN(TRIM(M3))=0,"",M3)

Or you can use this also,

=TRIM(J3 & K3 &","& L3&","&M3)

N.B. In the above example Cell J3 is not a Blank cell and please adjust the cell address as per your need.

5
  • 1
    Your first formula is substantially the same as Kevin's solution posted 20 hours before you. Second and third formulas doesn't add separator between items as shown in the question. Please only post an answer if it actually answers the question and it provides new information compared to existing answers. – Máté Juhász May 16 '18 at 11:33
  • @MátéJuhász, in fact I've not noticed Kevin's answer and in my second formula separate was Blank instead of Comma. Now I've edited both 2nd & 3rd Formula. And I've shown 3 different examples in context of larger scope. :-) – Rajesh S May 16 '18 at 12:02
  • The first formula is incorrect because it puts an undesired comma every place there is a missing value (what the OP is trying to correct), and no comma when there are actual values to separate. The second formula duplicates the problem the OP is trying to fix, and excludes a comma between J3 and K3 when both have values. If you actually created examples and tested these formulas with and without missing values, it would be immediately apparent that the answer is bad if you read the question. – fixer1234 May 18 '18 at 20:47
  • @fixer1234, both formulas are working properly, if u feel I can upload the Screen Shot . – Rajesh S May 19 '18 at 6:02
  • I posted an answer because so many people got this one wrong. Use the example I used of the possible combinations, and test your formulas against it. You'll see that neither formula in your answer does what the question asks. – fixer1234 May 19 '18 at 6:06

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.