Extracting a string up to a specific word (exact match)

  • Good afternoon folks. Probably a simple formula, but I can't seem to make any combination I can think of work. Here's situation:


    I have a column of records that have a combination of employee role and the area they work. I want to extract just their role into it's own column.

    Every cell has the same layout of role "at" and then the location

    The formula I have been working with to grab the role is:

    Code
    1. =Trim(Left(G2,Search("at",G4)-1))


    This works in most scenarios. The problem is that some of the roles have at within words such as Operations Manager, and so it will only grab "Oper".


    I need some formula that is looking for the exact "at" word such as in "Operations Manager at Quality department" where it finds the preposition and returns only that ahead of the preposition.


    Thanks in advance!