Tuesday, April 14, 2015

yogi_Split An Array Of Phrases With A Specified String As A Delimiter

                                           Google Spreadsheet   Post  #1944
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-14, 2015
question by JGM013:
Splitting text and re-joining it in Sheets
I am sure there is a straightforward way to do this. Essentially I am trying to split a text cell and rejoin portions of it on a condition.

The text cell to split will have two MLB team names with "at" in between them. I want to separate the team names into two separate cells getting rid of the "at". 

I have a test sheet showing what I am trying to accomplish. 

The data I have to split into two team names is in Column A. Using a simple Split command with a space as a delimiter populates Columns E thru K. What ideally I would like to get through this process for expect results is M1 & N1.

I am not sure if I can search an array, find the "at", separate and join everything both left and right of the "at" into one cell containing the full name. I am thinking I might need some sort of a Regex expression to accomplish this and that is beyond my knowledge of Sheets.

Thanks to anyone who has time to look at this.