troubleshooting Question

One more criteria to factor into a concatenated column output solution

Avatar of Andreamary
Andreamary asked on
Microsoft Excel
2 Comments1 Solution91 ViewsLast Modified:
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:

ORIGINAL POST:
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).

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

Thanks,
Andrea
EE-Sample-Concat-2Part.xlsx
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros