• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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
0
GeneBat
Asked:
GeneBat
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now