Google Sheets – Regular Expression to Wrap Phrases Between Commas with Carets

google sheetsregex

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?

Best Answer

This formula works:

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

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.

Related Solutions

Google Sheets Formatting – Word Wrap Cell Without Switching Modes

Google Spreadsheets should automatically wrap text when the content exceeds the width of the cell.

If not, you should be able to see a button on the toolbar to Wrap Text. Hit that when the focus is on the cell you want to wrap text for.

wrap text on toolbar

Alternatively you can force a linebreak in the cell with Ctrl + Enter.

Google-sheets – way to search and replace a regular expression in Google Docs spreadsheets

You can find them using find and replace if you go to view -> all formulas (ctrl+') and then do you "round( " find this will at least cut down on the manual labor of it all

I couldnt find a way (using find and replace to search for wild card value though

EDIT:

Try out the script below REF: http://www.google.com/support/forum/p/Google%20Docs/thread?tid=288d77c8c9ad7696&hl=en

function SearchReplaceInFormula() {

  var pattern = Browser.inputBox("Enter the search string:");
  var replacement = Browser.inputBox("Enter the replacement string:");
  if (pattern == "" && replacement == "")
  {
    Browser.msgBox("Nothing to search and or replace");
    return;
  }

  var formula = SpreadsheetApp.getActiveRange().getFormulaR1C1();
  var range = SpreadsheetApp.getActiveRange();
  var crows = range.getNumRows();
  var ccols = range.getNumColumns();

  var row = 0;
  for (row = 1; row <= crows; row++)
  {
       for (col=1; col <= ccols; col++)
       {
         var cell = range.getCell(row, col);
         var formula = cell.getFormula();
         var updated = formula;
         while(-1 != updated.indexOf(pattern))
         {
           updated = updated.replace(pattern, replacement);
         }
         if (formula != updated)
         {
           cell.setFormula(updated)
         }
       }
  }

  Browser.msgBox("Done replacing " + pattern + " with " + replacement);
}

Best Answer

Related Solutions

Google Sheets Formatting – Word Wrap Cell Without Switching Modes

Google-sheets – way to search and replace a regular expression in Google Docs spreadsheets

Related Topic