CODEWITHSUNDEEP
excel-formulaexcel-2010
Sage Mohan
Sage Mohan

Reputation: 155

Excel conditional concatenation

I have a few columns that am trying to concatenate based off a conditional statement. The columns I have looks like:

Col 1    Col 2    Col 3    Col 4    Col 5
  73      NA       NA       NA       NA

Am trying to concatenate cells that do not have "NA" or are blanks. I have the following line of code:

=IF(OR(B2<>"",B2<>"NA"),CONCATENATE(A2,",",B2),IF(OR(C2<>"",C2<>"NA"),CONCATENATE(A2,",",B2,",",C2),IF(OR(D2<>"",D2<>"NA"),CONCATENATE(A2,",",B2,",",C2,",",D2),IF(OR(E2<>"",E2<>"NA"),CONCATENATE(A2,",",B2,",",C2,",",D2,",",E2),0))))

The desired output is 

73

However, I keep getting

 73, NA

Note: Column 1 will always have a number.

Upvotes: 3

Views: 14464

Answers (3)

JPortillo
JPortillo

Reputation: 551

The TEXTJOIN function in Excel 2019+ allows ranges as parameters, which combined with an array formula becomes a conditional CONCATENATE or "CONCATENATEIF".

=TEXTJOIN(", ", TRUE, IF(A1:D1<>"NA", A1:D1, ""))

Instead of just hitting Enter, hit Shift + Ctrl + Enter. This will save it as an array formula, instead of a simple formula. It will be shown different in the formula box.

{=TEXTJOIN(", ", TRUE, IF(A1:D1<>"NA", A1:D1, ""))}

More info about array formulas here. Guidelines and examples of array formulas

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

Try this instead:

=LEFT(IF(AND(A1<>"",A1<>"NA"),A1 & ",","") & IF(AND(B1<>"",B1<>"NA"),B1 & ",","") &IF(AND(C1<>"",C1<>"NA"),C1 & ",","") &IF(AND(D1<>"",D1<>"NA"),D1 & ",","") &IF(AND(E1<>"",E1<>"NA"),E1 & ",",""),LEN(IF(AND(A1<>"",A1<>"NA"),A1 & ",","") & IF(AND(B1<>"",B1<>"NA"),B1 & ",","") &IF(AND(C1<>"",C1<>"NA"),C1 & ",","") &IF(AND(D1<>"",D1<>"NA"),D1 & ",","") &IF(AND(E1<>"",E1<>"NA"),E1 & ",","") )-1)

enter image description here

Edit:

It appears as if your data loads sequentially by column. You can simplify the formula to:

=A1 & IF(AND(B1<>"",B1<>"NA"),"," & B1,"")& IF(AND(C1<>"",C1<>"NA"),"," & C1,"")& IF(AND(D1<>"",D1<>"NA"),"," & D1,"")& IF(AND(E1<>"",E1<>"NA"),"," & E1,"")

Edit #2

If you are using Office 365 Excel then use TextJoin as an array formula:

=TEXTJOIN(",",TRUE,IF((A1:E1<>"NA")*(A1:E1<>""),A1:E1,""))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

Upvotes: 5

Just Rudy
Just Rudy

Reputation: 700

B2 is "NA", reverse the logic in your OR.

Upvotes: 0

Related Questions