One more criteria to factor into a concatenated column output solution

Posted on 2014-08-19
Last Modified: 2014-08-20
I received a great solution yesterday to a question I posed, but in using it today realized today that I had left out one potential example in the "desired results" column (G) in my sample spreadsheet. Occasionally the last 2 concatenated cells are blank, and when this occurs, the resulting concatenation has a unwanted dash at the end. To demonstrate this issue I'm hoping to get resolved, I have added this example to row 9, highlighted in yellow, in the attached spreadsheet.

Below is the original post, followed by the solution that I am currently using:

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).

=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)

Question by:Andreamary
    LVL 47

    Accepted Solution


    pls try

    =IF(AND(H9="VOL 1";OR(D9="CAD";D9="TREP"));B9&"-"&D9&"-"&LEFT(TRIM(IF(FIND(")";C9)<>LEN(C9);MID(C9;FIND(")";C9)+1;100);MID(C9;FIND(" ";C9)+1;100)));FIND(" ";TRIM(IF(FIND(")";C9)<>LEN(C9);MID(C9;FIND(")";C9)+1;100);MID(C9;FIND(" ";C9)+1;100))))-1)&IF(F9<>"";"-"&F9;"");B9&"-"&D9&IF(E9&F9="";"";"-"&E9&F9))

    Open in new window


    Author Closing Comment

    Perfect, Rgonzo...thanks very much!


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    746 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

    19 Experts available now in Live!

    Get 1:1 Help Now