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

formulasgoogle sheetsmicrosoft excelregex

The problem is that I entered a lot of formulas like =round(x+y) into a Google Docs spreadsheet. Now I need them exported to MS Excel and the latter only accepts =round(x+y,0) as a valid format. Since the doc holds a few 10s of this format – any suggestion on how to convert these would be highly appreciated (like if it can be done in MS Excel that's OK as well).

What I was thinking was a awk script sort of thing: find all round(*) replace with round(*,0)… of course this will have to ignore all the round(*,2) that I also have.

Tried to use the find and replace menu but it doesn't find round at all (and believe me it is there).

Best Answer

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);
}
Related Topic