Concatenated column output in 2 different ways depending on criteria as outlined

Posted on 2014-08-18
Last Modified: 2014-08-18
I would like Column G to concatenate different columns, including a specific string within one column, based on various criteria outlined below:

If D = "CAD" or "TREP", AND H = "VOL 1, then Column G to concatenate as follows:
G = B + D + C [first word after closing bracket OR first second word in string if no closing bracket] + F
Otherwise, Column G to concatenate as follows:
G = B + D + E + F

Format of output in Column G:
Dashes, but no spaces, between each value (as shown in attached spreadsheet example).

Question by:Andreamary
    LVL 27

    Accepted Solution

    You know the old saying:  "Be careful what you ask for; you just might get it."  

    Here's the formula:
    =IF(AND(H2="VOL 1",OR(D2="CAD",D2="TREP")),B2&"-"&D2&"-"&LEFT(TRIM(IF(FIND(")",C2)<>LEN(C2),MID(C2,FIND(")",C2)+1,100),MID(C2,FIND(" ",C2)+1,100))),FIND(" ",TRIM(IF(FIND(")",C2)<>LEN(C2),MID(C2,FIND(")",C2)+1,100),MID(C2,FIND(" ",C2)+1,100))))-1)&IF(F2<>"","-"&F2,""),B2&"-"&D2&"-"&E2&F2)

    Example workbook attached.


    Author Closing Comment

    Wow...very cool!! Thanks so much, Glenn. :-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    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…
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    794 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

    15 Experts available now in Live!

    Get 1:1 Help Now