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

2 Answers 2


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


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");

  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)

  Browser.msgBox("Done replacing " + pattern + " with " + replacement);
  • thanks for the solution (not what I expected and not perfect - but gets the job done!). Would have given you two up votes for the tip in the beginning (view all formulas) but can only up vote once :-)
    – Dror Cohen
    Jun 1, 2011 at 17:56

Please try:

Find: (.+)\)
Replace with: $1,0)
Search: This sheet (or to suit)
Search using regular expressions
Also search within formulae
Replace all.

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.