Search code examples
google-apps-scriptgoogle-sheetsgoogle-apps

Google Spreadsheets: Remove substring if string contains one of several strings

I have a spreadsheet that has the following column:

Alice Aikens Engineer
Bob Lob Law Engineer
Carrie Crenshaw Executive
Don Draper Dapper Executive
Elizabeth Elias
Fatrick Fitzgerald

I would like to have a second column where I would basically be able to remove that last word (Engineer, Executive) from the cells that contain those words. Not all cells end with those words though.

Any idea on how I can do this?

Solution

  • Assuming you have the data in Col A, try:

    =ArrayFormula(regexreplace(A1:A6, "Law Engineer|Engineer|Executive",""))

    and see if that works ?

    Note: you can simply expand the substrings to be removed by adding a "|" (means or) and then the substring. e.g:

    =ArrayFormula(regexreplace(A1:A6, "Law Engineer|Engineer|Executive|Dapper Executive",""))