If the prefix and suffix you are wanting to remove are static known values, then probably the easiest way to remove them is with REPLACE:
REPLACE(REPLACE(column,'ZZZ ',''),' (left)','')
I mean you could also do it with LEFT, RIGHT and LEN... something along the lines of:
RIGHT(LEFT(column, LEN(column) - 7),LEN(column)-11)
Now, side effects of the above 2 methods are
if your string in the middle that you want contains 'ZZZ " or ' (left)' it would get stripped out.
you need to toss a CASE onto the front of that to see if it starts with ZZZ and ends with (left) or you may get errors.
Depending on your source data would determine which method I recommend. Method 2 I anticipate to be a slower performance solution but less likely to remove data you want to keep. Method 1 should perform better and since it is a column of employee names, is unlikely to have "ZZZ" or "(left)" in a name so SHOULD be safe.