I have a string of data in one cell:  Membership and Volunteer Crew - do you like getting to know lots of new people? | Sauna Crew - Spend time sweating and supervising our sauna activity. | Crafters Crew - find and invite and present regional Crafts People at R&S and help them present workshops to our audience.I need it to become:  Membership and Volunteer Crew ::: Sauna Crew ::: Crafters Crew  in another cell on another sheet.Trying to figure out how best to say please replace everything between '-' and '|' with :::I am trying to create a Google Contact Group import friendly list.
Find and replace text in Google spreadsheet
google spreadsheets;google contacts
This is a job for regexreplace.Suppose A1 contains the text Membership and Volunteer Crew - do you like getting to know lots of new people? | Sauna Crew - Spend time sweating and supervising our sauna activity. | Crafters Crew - find and invite and present regional Crafts People at R&S and help them present workshops to our audience.Put the following in another cell=REGEXREPLACE(REGEXREPLACE(A1,-[^|]*\|,:::), - .*$,)The result is Membership and Volunteer Crew ::: Sauna Crew ::: Crafters CrewThe first application of regexreplace deals with text between - and |, replacing it by :::. The second one trims the end (from  -  to the end of the string), since you do not have | at the end of the original string.