Adding a new condition to an existing Excel formula

Attached is a sample spreadsheet implementing a formula (in Column J). I would like this formula revised. Column L shows the desired results I am looking for, with the new criteria that needs to be factored in highlighted in yellow (in row 4).  I've outlined the background details of the existing formula below and included the updated criteria change at the end.

CRITERIA OF EXISTING FORMULA:
I would like Column J 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" or "VOL 7" or "VOL 12" then Column J to concatenate as follows:
J = B + D + C [first word after closing bracket OR first second word in string if no closing bracket] + F
Otherwise, Column J to concatenate as follows:
J = B + D + E + F

CURRENT FORMULA:
=IF(AND(OR(H2="VOL 1",H2="VOL 7",H2="VOL 12"),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&IF(E2&F2="","","-"&E2&F2))

CRITERIA OF UPDATED FORMULA:
As above, but including the following new conditions:
If Columns G and Column H is blank, and Column I is not blank, then  J = B + D + E + F
Otherwise:
J = B + D + C [first word after closing bracket OR first second word in string if no closing bracket] + F

Thanks,
Andrea
EE-Sample-Updated.xlsx
AndreamaryAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
There are a couple of issues:
1) You refer to column H twice - once for the "Section" (actually in column H) and the "VOL" numbers (actually in column K).  The formula in the workbook refers to column K for the volume numbers; is that correct to continue?
2) The formula in the workbook is not the same as your example (does not contain the OR clause for the volume numbers 1,7,12).
AndreamaryAuthor Commented:
Sorry, Glenn!! I should have taken more time to review everything before posting!

I believe I've fixed the spreadsheet, and I've reuploaded it. Here is the current (fixed) formula in the spreadsheet:

=IF(AND(OR(K2="VOL 1",K2="VOL 7",K2="VOL 12"),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&IF(E2&F2="","","-"&E2&F2))
EE-Sample-Fixed.xlsx
Glenn RayExcel VBA DeveloperCommented:
This modification to your formula seems to work:
=IF(AND(NOT(AND(G2&H2="",I2<>"")),OR(K2="VOL 1",K2="VOL 7",K2="VOL 12"),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&IF(E2&F2="","","-"&E2&F2))

I highlighted the updated portion.  Since your condition of -
Columns G and Column H is blank, and Column I is not blank, then  J = B + D + E + F
is the same as the FALSE result of the original formula, its opposite
NOT(AND(G2&H2="",I2<>""))
should follow the TRUE result.

-Glenn
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Rory ArchibaldCommented:
FWIW, a little shorter:

=IF(AND(OR(G2&H2<>"",I2=""),OR(K2={"VOL 1","VOL 7","VOL 12"}),OR(D2={"CAD","TREP"})),B2&"-"&D2&"-"&LEFT(TRIM(MID(C2,FIND(IF(FIND(")",C2)<>LEN(C2),")"," "),C2)+1,100)),FIND(" ",TRIM(MID(C2,FIND(IF(FIND(")",C2)<>LEN(C2),")"," "),C2)+1,100)))-1)&IF(F2<>"","-"&F2,""),B2&"-"&D2&IF(E2&F2="","","-"&E2&F2))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Glenn RayExcel VBA DeveloperCommented:
Rory, I did not know you could do that (i.e., compare a value to an array of values)!  That's my cool knowledge for the week.  :-)

And darnit, I don't know why I didn't figure out the first OR clause like yours; it's much simpler and easier to follow.
AndreamaryAuthor Commented:
Both worked like a charm...thanks so much!
Andrea
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.