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