Combine Concatenated columns in One Column

Posted on 2012-09-06
Last Modified: 2012-09-06
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,
Question by:GeneBat
    LVL 92

    Expert Comment

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


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

    LVL 92

    Expert Comment

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

    LVL 39

    Accepted Solution

    To ignore the FALSE, you can use


    or shorter


    LVL 80

    Expert Comment

    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)
    LVL 80

    Expert Comment

    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)

    Author Closing Comment

    Works for me! Thanks again for the knowledge share.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now