2

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.

2

2 Answers 2

2

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 Crew

The 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.

1
  • This solution is ideal. Thank you very much.
    – Peter
    Apr 24, 2015 at 20:06
0

Surely CTRL + F works. Find and replace.

Search for - replace with ::: Search for | replace with :::

alternatively a formula would work.

=SUBSTITUTE(A1,"-",":::")

=SUBSTITUTE(A1,"|",":::")

The first replaces the - The second replaces any | IT assumes your data is in A1 and can be dragged down

1
  • But this doesn't leave: Membership and Volunteer Crew ::: Sauna Crew ::: Crafters Crew, it only replaces the -| for :::. Apr 23, 2015 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.