Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

One more criteria to factor into a concatenated column output solution

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
0
Andreamary
Asked:
Andreamary
1 Solution
 
Rgonzo1971Commented:
Hi,

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

Regards
0
 
AndreamaryAuthor Commented:
Perfect, Rgonzo...thanks very much!

Cheers,
Andrea
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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