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

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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:


    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!

  • Fluff13 good thought, though I had tried that earlier along with other combinations of spaces in front of and trailing the at, and it hits me with a #VALUE! error every time.

  • That would suggest your spaces are non-breaking spaces or something similar.

    What happens if you try

    =Trim(Left(G2,Search(" at ",G4&" at ")-2))

    Do you get the entire cell returned?

    If you can you please supply a sample workbook?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!