1

I have a Google Sheet with example information like this

Education, Conservation, Health, Human rights, Rural development, Poverty alleviation, Water and sanitation.
--------------------
Human rights, Democracy, Land reform, Entrepreneurship, ICT, Arts & culture

I need to quickly be able to wrap the phrases between the commas with a caret (^) in order for me to then import this sheet into a CRM.

Like this:

^Human rights^, ^Democracy^, ^Land reform^, ^Entrepreneurship^, ^ICT^, ^Arts & culture^

I've now read the Google Sheets documentation and looked and the Example Regular Expressions. I've now looked at numerous ideas on the stack sites and not finding exactly what I need.

Any thoughts?

0

This formula works:

=REGEXREPLACE(A1,"(\s|^)([^,]*)","$1^$2^")

enter image description here

Why does it work?

  • (\s|^) searches for a space or the beginning of the string
  • ([^,]*) searches for anything but a comma, as many times as possible
  • The parentheses makes sure these 'capture groups' are available in the replace part; $1 is the first capture group, $2 is the second one.

A tool like Regex101 may help discovering what a regular expression does, and experiment a bit yourself.

| improve this answer | |
  • thank you so much...saved a ton of time – Grow Jun 27 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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