asked on

Combine Concatenated columns in One Column

Hi Experts -

I'm trying to combine my four concatenated columns into one column. In other words, is it possible to do a IF/THEN with concatenation statements.

I want to (see example) place all of my concatened columns fields (AU, AV, AW, AX) in one column (AY).

Thanks in Advance,
GeneBat
Combine-Notifications.xlsx

Patrick Matthews

To simply concatenate them, use a formula like this in AY2:

=AU2&AV2&AW2&AX2

Or, if you want a delimiter (such as semicolon):

=AU2&";"&AV2&";"&AW2&";"&AX2

Patrick Matthews

Or if you mean "concatenate the values that are not false":

=MID(IF(AU2<>"FALSE",";"&AU2,"")&IF(AV2<>"FALSE",";"&AV2,"")&IF(AW2<>"FALSE",";"&AW2,"")&IF(AX2<>"FALSE",";"&AX2,""),2,1000)

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byundt

One formula for column AY that doesn't need helper columns AU, AV, AW or AX:
=SUBSTITUTE(IF(OR(AM2={"IS","ER"}),"-PO: [" & AL2 & "] ","") & "-QTY: [" & AN2 & "] " & "-P/N: [" & AO2 & "] " & IF(OR(AM2={"RS","IS"}),"-DEFECT: [" & IF(AM2="RS",AR2,AP2) & "] -LOCATION: [" & IF(AM2="RS",AS2,AO2) & "] ","") & "-TEXT: [" & AT2 & "]","-","",1)

byundt

The same formula could also be cast a smidge shorter using MID:
=MID(IF(OR(AM2={"IS","ER"}),"-PO: [" & AL2 & "] ","") & "-QTY: [" & AN2 & "] " & "-P/N: [" & AO2 & "] " & IF(OR(AM2={"RS","IS"}),"-DEFECT: [" & IF(AM2="RS",AR2,AP2) & "] -LOCATION: [" & IF(AM2="RS",AS2,AO2) & "] ","") & "-TEXT: [" & AT2 & "]",2,999)

GeneBat

ASKER

Works for me! Thanks again for the knowledge share.