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
GeneBatAsked:
Who is Participating?
 
nutschCommented:
To ignore the FALSE, you can use

=IF(AU2="FALSE","",AU2)&IF(AV2="FALSE","",AV2)&IF(AW2="FALSE","",AW2)&IF(AX2="FALSE","",AX2)

or shorter

=SUBSTITUTE(SUBSTITUTE(AU2&"|"&AV2&"|"&AW2&"|"&AX2,"|FALSE|","|"),"|","")

Thomas
0
 
Patrick MatthewsCommented:
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
0
 
Patrick MatthewsCommented:
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)
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
byundtCommented:
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)
0
 
byundtCommented:
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)
0
 
GeneBatAuthor Commented:
Works for me! Thanks again for the knowledge share.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.